Print this page

Difference between the total sum of the values on a report and the grand total of a custom summary formula

Knowledge Article Number 000233256
Description When a custom summary formula that summarizes a value is added to a report, please be mindful that the value that will appear in the grand total might not be the sum of the values that appear on the report.
Resolution This is due to the fact that the sum of the Formula Fields is not the sum of the values that are calculated in each report row: the Grand Total is calculated on its own at run time.

For example the following custom summary formula field is added to an opportunity report:
                          IF(AMOUNT.CONVERT:SUM<0, 0,AMOUNT.CONVERT:SUM)
The formula is shown as a column and on all grouping levels. The report is summary report format and has two groupings:
     - Grouping 1: Account name
     - Grouping 2: Opportunity owner

Only 3 opportunities are showing in the report, one for UserA that has the converted amount of -365.25 and two for UserB that have a total of converted amount of 401.
The report will show a big difference of amounts between the sum of the field amount converted (visible by adding the field as a column of the report, then opening the drop down menu of the column and selecting "summarize this field..." and flagging the checkbox "sum" and clicking on apply) and the formula above.
User-added image
As you can see on the screenshot above, being -365 a negative value, the formula will write 0 for the UserA.
For the UserB the value is not below zero, hence it will write the value of the two opportunity amounts summarized.

In the given example, when the formula needs to evaluate the values for the grand total, it will check if the total value is above or below zero and if not below zero, it will calculate the sum of all the opportunity amounts in the report (300+101-365 in this case) and not the sum of the values above zero (400,85+0).

For further information about the Custom Summary Formula, please review the article Build a Custom Summary Formula 
Another reason for the mismatch could be the Smart totaling, please review the article Report total doesn't match the total in Excel or in a Dashboard table component

promote demote