KNOWLEDGE BASE

Grand Totals Showing Unexpected Numbers with Certain Calculations


Published: 13 Mar 2017
Last Modified Date: 21 Jun 2018

Issue

When you turn on Grand Totals for a non additive aggregation, like CountD or Ratios, the totals may not show the expected number.

For example, if you are using an Excel file containing the following data: 
Screenshot of a spreadsheet with two columns, Region and Fruit
If you place Region on Rows and CNTD(Fruit) on Text, you will get a grand total of 3, where you would expect two fruits sold in each of two regions to add up to 4: 
User-added image

Environment

Tableau Desktop

Resolution

Option 1:

Select Analysis > Totals > Total All Using > SUM.
The Grand Total will now show as 4. 
User-added image

Option 2:

Create a Level of Detail based calculation and replace the measure in the view. The calculation for this example would look like the following:
Sum({Include : COUNTD([Fruit])})
The Count Distinct is calculated at the Region level and then the values are summed in the rows as well as the totals.
For more information about Level of Detail functions, see Level of Detail.

Cause

By default, Grand Totals will match the aggregation of the field being totaled.

Additional Information

If you are using Table Calculations or Blending please see the related links section.
Did this article resolve the issue?