KNOWLEDGE BASE

Customizing Grand Total to Show Different Measure Than Table


Published: 27 Jul 2016
Last Modified Date: 28 Mar 2018

Question

How to customize the Grand Total to show a different measure than the main table of the view, such as returning a Percent of Total in the Grand Totals while showing SUM([Sales]) in the view.

Environment

Tableau Desktop

Answer

The options below are demonstrated in the attached workbook and shown in the video.

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).

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 Format as percentage as desired.

To view these steps in action, see the video below: 

Note! The video has no sound.

Additional Information

For more information about using SIZE() to return a different measure in Grand Totals, see Customizing Grand Totals.
Did this article resolve the issue?