Grand Totals and Subtotals Do Not Show Expected Numbers With Table Calculations

Issue

When using table calculations, grand totals and subtotals might show unexpected results. The option to change the aggregation type of the grand total to something other than Automatic is grayed out.

For example, the view shows the difference in average regional sales between quarters. The automatic grand total is incorrect: it shows the difference between the average sales for the entire quarters, rather than the sum of differences between the average regional sales.

Environment

Tableau Desktop
Table calculations

Resolution

Option 1: Rewrite the calculation using LOD functions

In some cases it may be possible to rewrite the calculation using level of detail (LOD) functions in place of table functions. See Create Level of Detail Expressions in Tableau for more information.

After the calculation is rewritten then the grand total can be changed using the Total using setting. See Configure total aggregationfor directions.

Option 2: Add additional detail to the view and adjust table calculations

Totals are special rows that ignores the detail on the Rows shelf. Therefore to force the table calculation to be computed at the correct level of detail in the grand total, it will be necessary to add additional dimension(s) to the view. Afterwards all measures in the view will have to be modified to account for the additional dimension(s).

The following instructions begin from the worksheet "Example 1: original view" in the workbook in the right-hand Attachments section of this article. Directions for how to create the original view are embedded in the workbook.

For each dimension on the Rows shelf, right-click the dimension in the data pane and select Duplicate. In this example, the only [Region] is on the Rows shelf.
Drag [Region (copy)] to Detail on the Marks card
Create a calculated field:
1. Select Analysis > Create Calculated Field
2. In the Create Calculated Field dialog box, do the following and click OK:
3. Name the field. In the example, this field is called "New Difference Between Average Sales"
4. Enter the following formula:
```
IF FIRST() = 0  
THEN WINDOW_SUM([Difference Between Average Sales])  
END
```
Replace [Difference Between Average Sales] with [New Difference Between Average Sales] on Text on the Marks card
Right-click [New Difference Between Average Sales] in the view and select Edit Table Calculation...
In the Table Calculation dialog, do the following and close the dialog:
1. In the dropdown under Nested Calculations, select Difference Between Average Sales
2. Select Table (across) Note: this is the compute using setting for Difference Between Average Sales in the original view
3. In the dropdown under Nested Calculations, select New Difference Between Average Sales
4. Select Specific Dimensions
5. Check only the duplicated fields. In this example that is Region (copy)
Navigate to Analysis > Stack Marks > Off

Cause

Totals set to Total using > Automatic will compute the measure across the entire data set (as though the table in the view does not exist). Automatic grand totals will usually be correct if the table calculation uses only additive aggregations (e.g. SUM(), etc.. ). For example, the sum of each individual sale will be the same as the summing up the sums of sales per each customer.

If the table calculation refers to any fields that use a non-additive aggregation (e.g. division, multiplication, MIN(), AVG(), etc.) then the total will not equal the sum of the numbers in the view. For example, the average of each individual sale (AKA overall average) is different then summing up the average of sales for each customer.

Only Automatic totals are currently available for table calculations. To voice your support for the inclusion of this enhancement in a future product release, add your vote to the following Community Idea: Totals and subtotals over displayed marks instead of separate calc (aka make subtotals work like Excel)

Additional Information

If the issue is occurring on a field that does not use a table function, then it may be possible to change the Total using setting instead. See Configure total aggregation for directions.

Notes on Option 2

The attached workbook contains additional examples that cover additional use cases
The WINDOW_SUM() function mimics Total using > Sum. For other grand total aggregations use the corresponding table function (WINDOW_AVG() for average totals, WINDOW_MIN() for minimum totals, TOTAL() for automatic totals, etc...)
This example uses Column Grand Totals, which appear as a row in the view. For views that use Row Grand Totals, follow the same instructions replacing "row" with "column"
If the original view contains multiple measures, then all measures will have to be converted to a calculation like the one in step 3, even if the measures are not table calculations. See Example 2 in the attached workbook for more details.
In this example, the original table calculation was partitioned by all of the dimensions on the Rows shelf (AKA [Region] was unchecked in the Table Calculation dialog). If the original table calculation addresses a dimension that is duplicated, then in step 6-2 it will be necessary to check the duplicated field as well. See Example 4 in the attached workbook for more details.
Step 6 is setting separate compute using settings for the table function in [Difference Between Average Sales], which is LOOKUP(), and the table functions in [New Difference Between Average Sales], which is FIRST() and WINDOW_SUM(). For more information about how setting compute using settings for nested table calculations, see Customize Table Calculations