KNOWLEDGE BASE

Showing Different Calculation for Sub-totals and Grand Totals


Published: 18 Sep 2019
Last Modified Date: 19 Sep 2019

Question

How to show a different calculation for Sub-totals & Grand Totals. For example, showing SUM([Sales]) in cell, but SUM([Profit]) in Sub-totals & SUM([Quantity]) in Grand Totals.

Environment

Tableau Desktop

Answer

Step 1 - Create a calculated field

  1. Select Analysis > Create calculated field
  2. Name the calculated field "New Totals"
  3. Enter the following formula and click OK
    IF WINDOW_COUNT(ATTR([Category]))=0 THEN SUM([Quantity])
    ELSEIF WINDOW_COUNT(ATTR([Category]))=3 THEN SUM([Profit])
    ELSE SUM([Sales])
    END

Build the view

  1. Drag Category and Sub-Category to Rows
  2. Drag the new calculated field to Text
  3. Click Analysis > Add subtotals and Grand Totals
The above steps can be reviewed in the attached workbook. 

Important: The result of window_count depends on the data and the view, and such results will be effected by filters. We recommend using this workaround after sufficient testing.
Did this article resolve the issue?