Loading

Report total doesn't match the total in Excel or in a Dashboard table component

Publiseringsdato: Oct 13, 2022
Beskrivelse

A report on Opportunities with Opportunity Products, grouped by Product Family, returns the total of the Amount values. On occasion, when users export a report like this and summarize the Amount, the exported results may differ from that which is displayed within the report when viewing via the User Interface. Similarly, when users create a Dashboard table component for this report, the total displayed is higher than the Report's total. 

This behavior is due to a feature called Smart Totalling. 

Løsning

Why the totals don't match

  • Some Opportunities have Products in more than one Product Family.
For example: Opportunity "X" may have one Product in the Product Family "A" and one in the Product Family "B". Let's say the Amount of Opportunity "X" is $500, of which $300 is given by Product "A" (Product Family "A"), and $200 is given by product "B" (Product Family "B"). Because you're grouping the report by Product Family, the same Opportunity record "X" will be included twice in the report: once for Product Family "A" and once for Product family "B"
  • If you're summarizing the Opportunity Amount, the entire Amount ($500) will be displayed twice; Once under Product Family "A" and once for Product family "B"
Product Family - Amount
A........................... $500
B........................... $500
Grand Total......... $500
  • When returning the report's Grand Total, however, Salesforce uses a feature called "Smart Totaling". This means that, when a report includes duplicate data like in the example above, duplicate data is counted only once.
  • When you export the report into a spreadsheet and then summarize the Amount, you will get $500 + $500 = $1000. This is due to the fact that the spreadsheet does not take into account that the two $500 entries are related to the very same Opportunity. E.g. the spreadsheet does not take into account the duplication.
  • When you create a Dashboard table component based on this Report, and you choose to display the Grand Total, you'll see that it's also shown as $1000. This is because Smart Totaling works for Reports, but NOT for Dashboards.
 
​Important: Table Components are the only components that don't utilize Smart Totaling.

 

How to fix it


Make all data consistent by summarizing the Total Price (the value of each Product) instead of the Amount (the value of each Opportunity). This will ensure that each row (and each grouping level) only displays the value associated with each Product, rather than the entire Opportunity Amount.
 

Product Family - Total Price
A.............................. $300
B.............................. $200
Grand Total............ $500
 
NOTE: Number and Currency fields can be set to contain X number of decimal places. If the actual value of the field contains more than X decimal places, the actual value will be used to calculate the Grand Total in the report. The report exports the displayed value (X decimal places) as defined in the organization field setup. For this reason, if a calculation is done based on the displayed value, it may vary from the Grand Total on the report. 


Some customers have requested the ability to disable the Smart Totaling feature.  While this is not currently on our roadmap, we suggest promoting this feature request on our Ideas Exchange. 
Knowledge-artikkelnummer

000385191

 
Laster
Salesforce Help | Article