KNOWLEDGE BASE

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


Published: 03 Jul 2013
Last Modified Date: 06 Mar 2017

Issue

When using table calculations, grand totals and subtotals might not show expected results. The option to change the aggregation type of the grand total to sum is grayed out.

Environment

  • Tableau Desktop
  • Table calculations

Resolution

The following instructions can be reviewed in the attached workbook.

Step 1: Create a non-additive Calculation

  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 "Non-Additive Calc" and enter the following formula: 
    SUM([Sales]) / SUM([Profit])

Step 2: Create a Table Calculation that relies on a non-additive 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 "Table Calculation" and enter the following formula: 
    ZN([Non-Additive Calc]) - LOOKUP(ZN([Non-Additive Calc]), -1)

Step 3: Build the view

  1. Drag "Category" to the Columns
  2. Drag Measure names to Filter and Keep the newly created calculated fields.
  3. CTRL + Drag Measure Names to Rows
  4. Drag Order Date to Rows as well.
  5. Drag Measure Names to the view
  6. Right-click on Table Calculation and select Compute using > Table (down)
  7. (Optional) Right-click Measure Values and select Format > Numbers section > Number (Custom) > Set Decimal to 1.
NOTE: In the view, it is expected that the Table calculation shows 27.8 + (-)1.3 + (-)3.3 = 23.2 for the year 2013. However it shows -2.14 which corresponds to: <this year's grand total> - <last year's grand total> 

Step 4: (Optional) Duplicate the sheet 

Right-click the sheet and select Duplicate sheet to compare the results.

Step 5: Fix the Grand Total for Table Calculation

Duplicate the Measure on which the data relies on, in this example: Category.
  1. In the Dimension Data Pane, right-click the "Category" dimension and select Duplicate
  2. Add this duplicated dimension named "Category (copy)" to Details
    NOTE: This should cause all the values in the table to also appear in the Grand Total box, so that multiple numbers appear in one Grand Total cell in the view.
  3. 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 "Non-Additive Calc" and enter the following formula: 
      IF FIRST() = 0 
      THEN 
      WINDOW_SUM([Table Calculation]) 
      END
  4. Replace the original measure in the view, example [Table Calculation], with [Table Calculation GT Fix].
  5. Right click on [Table Calculation GT Fix] in the view and choose Edit Table Calculation
  6. Calculated Field selection is set to [Table Calculation Grand Total Fix], choose to set Compute Using: Category (copy).
NOTE: If the number is still not right, double check that when the Calculated Field drop down has [Table Calculation] selected, the Compute Using is set as needed (usually Table Across or Table Down, but never the dimension copy created earlier). More information can be found below in the Additional Information section of this article. 
 
To view these steps in action, see the below video: 

Cause

Grand Total set to "Automatic" computes the measure across the entire data set (as though the table in the view does not exist).

If the measure in the view has an aggregation applied, such as SUM(measure) or MIN(measure), then Grand Total automatically applies the measure's aggregation to the measure across the entire data set. Starting in Tableau Desktop 8.1 the Grand Total aggregation can be changed by switching "Automatic" to "Sum", "Average", etc., per Grand Totals and Aggregations.

When the measure in the view is a Table Calculation, then the field will appear as AGG(table calculation) and the aggregation type of the Grand Total cannot be changed. Also, if the Table Calculation refers to any fields that use a nonadditive or semiadditive (examples: divide, multiply, MIN(), AVG(), etc.), then the Grand Total will not equal the sum of the numbers in the view. In other words, if using a Table Calculation with fields that use SUM() and + or - only, the Grand Total may appear correct due to the additive functions computing across the entire data set in a similar manner to adding the numbers in the view. If using a Table Calculation with fields that use MIN() or multiply (*), then the Grand Totals will probably appear incorrect.

Additional Information

Note: When  editing the table calculation of [Table Calculation GT Fix] to show the grand total, you can confirm the result by following the steps below if the results are inaccurrate:
  1. Right click on [Table Calculation GT Fix] in the view and choose Edit Table Calculation
  2. In the Table Calculation dialog, do the following and click OK:
    1. In the Calculated Field dropdown, select Table Calculation
    2. In the Compute using dropdown, select Table (Down). NOTE: By default Compute using for [Table Calculation] nested inside of [Table Calculation GT Fix] will be set to the same setting as [Table Calculation] in the view.
    3. In the Calculated Field dropdown, select Table Calculation GT Fix
    4. In the Compute using dropdown, select Category (copy)
 
If your view is structured differently then you may need to use advanced table calculation settings. For more information see Table Calculations: Addressing and Partitioning." 
Did this article resolve the issue?