Grand Totals Showing Unexpected Numbers with Certain Calculations

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

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.