KNOWLEDGE BASE

## Customizing Totals to Show Different Values Than Table

Published: 27 Jul 2016

### 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

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
1. 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
```
2. Place the calculated field [Custom GT] in the view.
3. 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
1. Create a Calculated Field called "Size" using the following formula:
`SIZE()`
2. Create a Calculated Field called "Custom GT" using the following formula:
```IF [Size] > 1
THEN SUM(Sales)
ELSE SUM(Sales)/TOTAL(SUM(Sales))
END
```
3. Build the view with the desired dimensions.
4. Place the calculated field [Custom GT] in the view.
5. 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.
6. Right click on [Custom GT] in the Marks card and select Format...
7. In the left-hand Format pane, Under Totals, in the Numbers dropdown, select Percentage
CLICK TO EXPAND STEPS
##### Option 3: Customize Subtotals
1. Create a Calculated Field called "Size" using the following formula:
```SIZE()
```
2. Create a Calculated Field called "Number of " using the following formula:
`TOTAL(COUNTD([Region]+[Category]))`
3. Create a Calculated Field called "Custom GT" using the following formula:
```IF [Size] <= [Number of Subtotals]
THEN AVG([Sales])
ELSE SUM([Sales])
END
```
4. Build the view with the desired dimensions.
5. Place the calculated field [Custom GT] in the view.
6. 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.
7. Right click on [Custom GT] in the Marks card and select Format...
8. 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.