KNOWLEDGE BASE

Displaying Only Selected Totals


Published: 19 Jun 2013
Last Modified Date: 12 Apr 2023

Question

How to display some totals (subtotals or grand totals) and hide other totals.

Environment

  • Tableau Desktop
  • Tableau Server

Answer

Depending on which totals should be hidden the solution will vary. The workbook in the right-hand pane of this article uses the sample data set Superstore to demonstrate all of the following solutions.

All solutions start from the "original" worksheet. Directions to create "original" are in the workbook.

Scenario 1: Remove subtotals for a dimension

Option 1: Tableau Desktop Only
In a view that has the three dimensions Category, Sub-category, and Segment on the Rows shelf, display subtotals only for Category.
  1. Select Analysis > Totals > Add All Subtotals.
  2. To remove unwanted subtotals, right-click Sub-Category on the Rows shelf and then uncheck Subtotals.
To view theses steps in action, see the video below: 
Please note: The videos may have been created in an older version.

Option 2: Tableau Desktop or Web Edit
  1. Add only the dimensions that you want subtotals for to the view.
  2. Select Analysis > Totals > Add All Subtotals.
  3. Add any additional desired dimensions to the view.

 

Scenario 2: Hide total values for a measure

In a view with the measures Sales and Profit, display total values only for Sales. These steps only work in Tableau Desktop, and there is not currently a web-authoring alternative.
  1. Right-click Profit on the Measure Values card and select Total Using > Hide.
To view these steps in action, see the video below: 

Scenario 3: Hide total value for dimension value

In a view with subtotals for Category and Sub-Category, hide total values for specific dimension values. Note, the total row or column will still show as a blank row.
  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Sales (hide some total values)"
    2. In the formula field, create a calculation similar to the following:
      IF MIN([Segment]) != MAX([Segment])
      //TRUE for all total rows for segment or higher in the view hierarchy
      AND MIN([Sub-Category]) = MAX([Sub-Category])
      //TRUE for all rows in view except totals for sub-category or higher in the view hierarchy
      AND MIN([Sub-Category]) IN ('Bookcases','Appliances')
      //list subtotals to hide
      THEN NULL
      
      ELSEIF MIN([Sub-Category]) != MAX([Sub-Category])
      AND MIN([Category]) = MAX([Category])
      AND MIN([Category]) = 'Furniture'
      THEN NULL
      
      ELSE SUM([Sales])
      END
      
  3. Create a calculated field with a name like "Profit (hide some total values)" with almost the same calculation as [Sales (hide some total values)] except returning SUM([Profit]) instead of SUM([Sales]), similar to:
    IF MIN([Segment]) != MAX([Segment])
    AND MIN([Sub-Category]) = MAX([Sub-Category])
    AND MIN([Sub-Category]) IN ('Bookcases','Appliances')
    THEN NULL
    
    ELSEIF MIN([Sub-Category]) != MAX([Sub-Category])
    AND MIN([Category]) = MAX([Category])
    AND MIN([Category]) = 'Furniture'
    THEN NULL
    
    ELSE SUM([Profit])
    END
    
  4. Drag Sales (hide some total values) on top of SUM(Sales) on the Measure Values card to replace it.
  5. Drag Profit (hide some total values) on top of SUM(Profit) on the Measures Values card to replace it.

Scenario 4: 'Remove' subtotal for dimension value

This solution makes it look like the view on the dashboard does not have a subtotal row for Furniture by creating two worksheets and combining them on the dashboard.
Create multiple worksheets
  1. Right-click the tab for the "Original" worksheet and select Duplicate.
  2. Rename the duplicate worksheet to "Other".
  3. Drag Category to the Filter shelf.
  4. In the Filter dialog, check everything except Furniture and click OK.
  5. Right-click the tab for the Other worksheet and select Duplicate.
  6. Rename the duplicate worksheet to Furniture.
  7. Right-click Category on the Filters shelf and select Edit.
  8. In the Filter dialog, select only Furniture and click OK.
  9. Navigate to Analysis > Totals > Remove all Subtotals.
  10. Navigate to Analysis > Totals > uncheck Show Column Grand Totals.
Create and format dashboard
  1. Create a new dashboard.
  2. Add a vertical layout container to the dashboard.
  3. Add the Furniture and Other worksheets inside of the vertical layout container.
  4. Click Furniture on the dashboard so it has the selected gray outline, and navigate to Layout in the left-hand pane.
  5. Open the Outer Padding menu in the Layout pane and adjust the bottom padding to zero.
  6. Repeat steps 4-5 to set the top outer padding for "Other" to zero.
  7. In the left-hand pane, navigate to the Dashboard tab.
  8. In the Size menu, increase the height until neither view has vertical scrollbars.

Additional Information

  • "Add All Subtotals" only adds subtotals to the dimensions currently in the view. 
  • To enable "Add All Subtotals" use more than one dimension in your view.

Totals are currently at the field level. To voice your support for the inclusion of toggling totals at the dimension value level in a future product release, add your vote to the following Community Idea: Option to eliminate subtotalling single rows

 
Did this article resolve the issue?