KNOWLEDGE BASE

Customizing Grand Total to Show Different Measure Than Table


Published: 27 Jul 2016
Last Modified Date: 22 Dec 2016

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

Option 1: To show a different measure: 

  1. Create a Calculated Field called "Custom GT" using the following formula:
  2. 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: 
    1. IF Size() > 1
      THEN MAX([Profit])
      ELSE SUM([Sales])
      END
      
  3. Place the calculated field [Custom GT] in the view.
  4. Right click the calculated field [Custom GT] and select Edit Table Calculation... and select Pane (across and then down).

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

  1. Create a Calculated Field called "Size" using the following formula:
  2. SIZE()
    
  3. Create a Calculated Field called "Custom GT" using the following formula:
  4. IF [Size] > 1
    THEN SUM(Sales)
    ELSE SUM(Sales)/TOTAL(SUM(Sales))
    END
    
  5. Build the view with the desired dimensions.
  6. Place the calculated field [Custom GT] in the view.
  7. Right click on [Custom GT] in the Marks and select Edit Table Calculation...
    1. In the Table Calculation dialogue, set the Nested Calculations to [Size]
    2. Under Compute Using choose Pane (across and then down) and click OK.
  8. Right click on [Custom GT] in the Marks and Format as percentage as desired.

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

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?