How to Correct Grand Totals and Subtotals When Using Table Calculations in Tableau

Опубликовано: 03 Октябрь 2024
на канале: Tableau

61,189

In this silent video you’ll learn how to correct grand totals and subtotals in Tableau when using table calculations.

Read the full article here: Grand Totals and Subtotals Do Not Show Expected Numbers With Table Calculations — http://kb.tableau.com/articles/issue/...

- - - - - - - - - - - - - -

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:
+Name the field. In the example, this field is called "Non-Additive Calc"
+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:
+Name the field. In the example, this field is called "Table Calculation"
+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)
(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
1.Duplicate the Measure on which the data relies on, in this example: Category.
2.In the Dimension Data Pane, right-click the "Category" dimension and select Duplicate.
3.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.
4.Create a calculated field:
Select Analysis - Create Calculated Field
In the Create Calculated Field dialog box, do the following and click OK:
+Name the field. In the example, this field is called "Non-Additive Calc"
+Enter the following formula:
IF FIRST() = 0
THEN
WINDOW_SUM([Table Calculation])
END
5.Replace the original measure in the view, example [Table Calculation], with [Table Calculation GT Fix].
6.Right click on [Table Calculation GT Fix] in the view and choose Edit Table Calculation
7.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).

- - - - - - - - - - - - - -

Subscribe to the Tableau YouTube channel here: [link: http://www.youtube.com/subscription_c...]

Free Trial of Tableau: [link: https://www.tableau.com/products/trial ]