KNOWLEDGE BASE

Customizing Totals to Show Different Values Than Table


Published: 27 Jul 2016
Last Modified Date: 15 Aug 2018

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

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 Unable to Change Format When Using Parameters to Swap Fields
  • 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.
Did this article resolve the issue?