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:
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.
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