How to calculate percentage of total expenses and other comparisons between accounts
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:
- Add a second CBR with a blank value for Related Formulas / Rows / Units at the end of the section of the report you are calculating if you want to stop the CBR calculation.
- Add multiple CBR rows if you want to perform multiple calculations or perform calculations per section of your report.
- Prevent printing of the CBR calculation for specific rows using Column Restriction in the Row Definition. This is especially useful for preventing CBR calculation on rows that don't make sense like a row already calculating gross margin percentage.
Watch the video below for more information.