Customizing Totals to Show Different Values Than Table

Published: 27 Jul 2016
Last Modified Date: 05 Feb 2024

Question

How to customize the Grand Total or Subtotals to show a different value than the main table of the view. For example, returning a Percent of Total in the Grand Totals while showing SUM([Sales]) in the view.

Environment

Tableau Desktop

Answer

Use the function SIZE() to differentiate between different parts of the view in a calculation. The attached example workbook uses the sample data set Superstore to demonstrate the following directions:

CLICK TO EXPAND STEPS

Option 1: Showing a different measure

Create a Calculated Field called "Custom GT" using the following formula:
```
IF Size() > 1
THEN [<main table view measure>]
ELSE [<grand total measure>]
END
```
- For example, using the Sample Superstore data source, we want to see the Maximum profit per category and sub-category while still viewing the total of Sales. The calculated field would look like:
```
IF Size() > 1
THEN MAX([Profit])
ELSE SUM([Sales])
END
```
Place the calculated field [Custom GT] in the view.
Right click the calculated field [Custom GT] and select Edit Table Calculation... and select Pane (across and then down).

CLICK TO EXPAND STEPS

Option 2: Showing a table calculation, such as percent of total

Create a Calculated Field called "Size" using the following formula:
```
SIZE()
```
Create a Calculated Field called "Custom GT" using the following formula:
```
IF [Size] > 1
THEN SUM(Sales)
ELSE SUM(Sales)/TOTAL(SUM(Sales))
END
```
Build the view with the desired dimensions.
Place the calculated field [Custom GT] in the view.
Right click on [Custom GT] in the Marks card and select Edit Table Calculation...
- In the Table Calculation dialogue, set the Nested Calculations to [Size]
- Under Compute Using choose Pane (across and then down) and click OK.
Right click on [Custom GT] in the Marks card and select Format...
In the left-hand Format pane, Under Totals, in the Numbers dropdown, select Percentage

CLICK TO EXPAND STEPS

Option 3: Customize Subtotals

Create a Calculated Field called "Size" using the following formula:
```
SIZE()
```
Create a Calculated Field called "Number of " using the following formula:
```
TOTAL(COUNTD([Region]+[Category]))
```

Create a Calculated Field called "Custom GT" using the following formula:

IF [Size] <= [Number of Subtotals]
THEN AVG([Sales])
ELSE SUM([Sales])
END

Build the view with the desired dimensions.
Place the calculated field [Custom GT] in the view.
Right click on [Custom GT] in the Marks card and select Edit Table Calculation...
- In the Table Calculation dialogue, set the Nested Calculations to [Size]
- Under Compute Using choose Table (down) and click OK.
Right click on [Custom GT] in the Marks card and select Format...
In the left-hand Format pane, Under Totals, in the Numbers dropdown, select Percentage

To view the steps showed in the below video, please expand the above section.
Note: the video has no sound.

Additional Information

The totals can only be formatted separately from the default if there is only one measure in the view. If it is necessary to format totals differently than the table (e.g. decimal vs. percentage) when there are multiple measures in the view then see option 2 in Change Formatting for Different Values Based on Parameter Selection
The function SIZE() will return the number of rows in a part of the view. As grand total only has one row, SIZE() will always return 1 for the grand total. The size for other parts of the view, such as subtotals, will change depending on how many rows are in the view.

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