Dynamics Financial Reporting

All about financial reporting and consolidations for Microsoft Dynamics ERPs

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

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

  • Comments 4

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.

Leave a Comment
  • Please add 8 and 4 and type the answer here:
  • Post
  • 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)

    Thank you

  • @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.

Page 1 of 1 (4 items)