Did you know that you can calculate the percentage of total expenses for all individual expense lines? The Change Base Row (CBR) format code lets you identify a row as the base row, and then calculate what percentage other accounts are in relation to the base row. This type of report is commonly used to identify which accounts are having the largest impact on income or expenses.
You can setup this calculation in a few steps.
1. In the Row Definition add a CBR format code above the row where you want to start the calculation.
2. Set the Related Formulas / Rows / Units by entering the Row Code of your baseline row.
3. In the Column Definition add a CALC column. Set the calculation to B/BASEROW. The calculation is taking the values in column B and dividing them by the BASEROW we identified in the Row Definition for column A.
4. Set the Format / Currency Override for column B to be formatted as a percentage.
If you want to get more sophisticated you can add in any of the following formatting features:
Watch the video below for more information.
What Column Restictions can be used in the Row Definitions to prevent CBR calculations on specific rows? We have a report that states statistical information then P&L and we want to restrict the CBR calculation on the statistical information.
Good Stuff Ryan, I have been able to implement this little step,it is exactly what I was looking for. Question for you, how do you supress a 0.00% from being reflected on the report, due say to no Currnet YTD #'s for a particular income statement line item??
I understand how this works for one column, or one column at a time.
How do you set up a Column definition to show the percentage for an aggregate of monthly totals? That is, total Jan and Feb expenses in a column, then show the percentage of Jan+Feb expenses with respect to the Total expense for those two months. (I'm actually trying to do this over 12 months, but I'll take advice on the two month scenario for now)
In the Row Definition, you can use the column restriction to effectively say don't print the CBR column D in these rows.
To not show 0.00%, you can either use the setting in the Report Definition for "Display blanks for zero amounts" or modify the default format override. Double Click in Format/Currency Override and select Percentage. Then enter a "space" in the zero value override text and you will see an updated preview in this window.
Is there a way to get % on the Account Level reports? All of our Sales are recorded in 1 department of the tree, which is fine on the Financial Level. We would like to get the % of total company sales down at the unit(department) level, but since there are no sales recorded in most departments, the % is 0.00% on all of the units. I tried non printing rows and IF @UNIT, but the rollup keeps messing up the summary units. Any suggestions on how to get the % on the Account Level reports based on Total Company Sales, not the sales of the specific unit?