Freigeben über

How to calculate percentage of total expenses and other comparisons between accounts

  • Artikel

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.

[embed]https://www.youtube.com/watch?v=yuYJX6U47cU&feature=youtu.be[/embed]

Comments

  • Anonymous
    July 02, 2013
    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.

  • Anonymous
    April 21, 2014
    The comment has been removed

  • Anonymous
    May 06, 2014
    The comment has been removed

  • Anonymous
    May 21, 2014
    @JillE, In the Row Definition, you can use the column restriction to effectively say don't print the CBR column D in these rows. blogs.msdn.com/.../using-column-restrictions-to-improve-your-report-presentation.aspx @Mark, 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.

  • Anonymous
    May 26, 2015
    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?