How to exclude certain data only from grand total

Published: 14 Jun 2023
Last Modified Date: 15 Jun 2023

Question

How to exclude certain data only from grand total.

<Current Behavior>
Sales by all Category is included in grand total
User-added image

<Desired Behavior>
If you select "Furniture" in parameter, Furniture Sales is excluded from grand total.
And change the title according to parameter value.

Environment

Tableau Desktop

Answer

Please follow along with the attached sample workbook to the right of this text in the Attachments section.

<STEP 1>
Create the Crosstab.

1. Connect to the Sample Superstore from Tableau Desktop.
2. Drag Category to Rows .
3. Drag Sales to Text on Marks.
User-added image

4. From the Toolbar, select Analysis > Totals > Show Column Grand Totals.

<STEP 2>
Create the calculation that excludes certain data only from grand total.
5. Create a new parameter as following.
User-added image

6. Right click on [Select Category Excluded from Total] -> Show parameter.
7. Create a new calculated field as following.
Name :Total Sales
Calculation : {FIXED :SUM([Sales])}
8. Create a new calculated field as following.
Name :Sales Excluded from Total
Calculation :{FIXED :SUM(IF [Category]=[Select Category Excluded from Total] THEN [Sales] END)}
9. Create a new calculated field as following.
Name :Grand Total Excluded Certain Category
Calculation :IF WINDOW_MIN(MIN([Category])) = WINDOW_MAX(MAX([Category]))
THEN SUM([Sales])
ELSE SUM([Total Sales]) - SUM([Sales Excluded from Total])
END
10. Drag [Grand Total Excluded Certain Category] on [Sales] placed in Text.
User-added image

11. Right click on [Grand Total Excluded Certain Category] placed in Text -> Edit Table Calculation -> Set as following.
User-added image

<STEP 3>
Change the Title according to parameter value.
12. Double click on Title -> Enter the following sentence.
Grand Total Excluded <Parameters.Select Category Excluded from Total> Sales
User-added image