Showing Different Calculation for Sub-totals and Grand Totals

Published: 18 Sep 2019
Last modified date: 15 Sep 2023

Question

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

Environment

Tableau Desktop

Answer

Follow along with the attached packaged workbook to review the steps below. The workbook can be found to the right of this text in the "Attachments" section.

Step 1 - Create a calculated field

Select Analysis > Create calculated field
Name the calculated field "New Totals"

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

Drag Category and Sub-Category to Rows
Drag the new calculated field to Text
Click Analysis > Totals > Show Column Grand Totals
Click Analysis > Totals > Add All Subtotals

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.