Choosing your calculation priority for totals and calculations

Report Definitions in Management Reporter have a setting for Calculation priority on the Settings tab. By default the setting has row calculations being performed first and then column calculations. Most people do not even know about this setting because 95% of the time you never need to change it. However, every now then you have a report with intersecting calculations and your totals or calculations are incorrect. When this happens you need to change the setting to calculate column calculations first and row calculations second. Let's look at a common example.

This report is a trended income statement that shows Gross Profit % and a Total column that adds together all the months. When you look at the Gross Profit % total line it is incorrect, showing a gross profit percentage of 1102%.

Figure 1: Trended Income Statement with incorrect Gross Profit % total

The row definition has a simple calculation for the Gross Profit % and the column definition has a calculated column for the total.

Figure 2: Row and column definition

To correct the Gross Profit % total, you need to change the Calculation priority setting in the Report Definition to Perform column calculation first and then row.

Figure 3: Report definition

Now the report will show you the correct total for the Gross Profit %.

Figure 4: Trended Income Statement with correct Gross Profit % total

Changing the calculation priority when there are intersecting calculated and total rows will save you time from creating complex calculations to get around the issue.

Comments

• Anonymous
  May 13, 2014
  This is a timely article for me. I have a report with many of these seem functions and with additional columns to calculate dollar and percentage variance. I seem to have created a scenario where either my profit percentage calculated in a row like yours, or my variance calculated columns can be correct, but not both. Have you seen that before?

• Anonymous
  May 13, 2014
  Hello Marshall, I have seen this before and usually the calculation priority resolves it. If this isn't the case we may want to look at the report specifically. One thing is do you want the profit % printing in variance % column? Many people use the column restriction and exclude it from that column.

• Anonymous
  June 02, 2014
  Thanks for responding. Changing the priority fixes one issue, but creates other calculation problems. I am having to choose which information to show accurately in each report because I can't get both. Other examples are ROIC %'s and Days sales outstanding calculations within a report. For example, I have a report showing 3 columns. Actual, budget, variance. ROIC is 3.89% Actual, 3.17% budget and the variance shows 34.47%. If I change the calc priority the variance will calculate correct, but the ROIC calculations in the report are not correct. It would be nice to show a variance of .72.

• Anonymous
  June 04, 2014
  I also have the same issue of having to chose the lesser of two evils in calculating columns first or rows.  I'm interested to hear if there is a way around this, but I'm going to look at that column restriction that April mentions.

• Anonymous
  June 06, 2014
  Marshall and Brad, You may be able to get around with non-printing and placement, but I would have to test it out. I would definitely work with support on the issue as they will be able to work through it in a timely fashion. You can also send me the .tdbx and I can look at it, but it could be a few weeks. If you decide to my email is aolson@microsoft.com