KNOWLEDGE BASE

Displaying a Value, Subtotal, and Grand Total For Different Levels of Granularity in the Same View


Published: 31 Dec 2013
Last Modified Date: 24 Jul 2017

Question

How to display the value, subtotal and grand total for different levels of granularity in the same view. 

Environment

Tableau Desktop

Answer

Use Table Calculations to display the subtotal and grand total values and use a lookup calculation to filter the view. Using the Excel Superstore subset data source sample, the following instructions can be reviewed in the workbook attached on the right side of this page. 

Step 1 - Build the View

  1. Drag Profit to Text
  2. Drag Category to Columns.
  3. Drill down on Category, so that the it expands to Sub-Category.
  4. Drag Region to the Rows shelf.

Step 2 - Create calculated fields

  1. Create a new calculated field to display the Category Profit per Region.
  2. Go to Analysis > Create Calculated Field.
    1. Enter a name, e.g. 'Category Total'.
    2. Enter the formula: WINDOW_SUM(SUM([Profit]))
    3. Click OK.
  3. Drag the newly created calculated field to the view.
  4. Repeat step 2 to create a new calculated field to display the grand total.
    1. Enter a name, e.g. 'Region Grand Total'.
    2. Enter the formula: WINDOW_SUM(SUM([Profit]))
    3. Click OK.
  5. Drag the newly created calculated field to the Measure values list.
  6. Repeat step 2 to create a calculated field to filter what category will be displayed.
    1. Enter a name, e.g. 'Lookup Sub-Category'.
    2. Enter the formula: LOOKUP(MIN([Sub-Category]),0)
    3. Click OK.

Step 3 - Adjust the view

  1. Move SUM(Profit) value to the top of the Measure Values list
  2. Move Measure Names from Rows to Columns.
  3. In the Measure Values list, right click on "Category Total" and select Compute Using > Category.
  4. In the Measure Values list, right click on "Region Grand Total" and select Compute Using > Table (across).
  5. Drag Lookup Sub-Category to Filters, select a dimension and click OK.
  6. Right-click on Lookup Sub-Category and select Show Filter.

Optional

  1. To change the filter display options, click the drop-down menu on the filter .
  2. Right-click Measure Names and select Edit Aliases to change the headers in the view.
  3. Right-click on the Category/Sub-Category header on the view and select Hide Field Labels for Columns.
  4. Right-click on Sub-Category on the Columns shelf and clear Show Header.
  5. Right-click on Category on the Columns shelf and clear Show Header.

Additional Information

To view these stepss in action, see the video below: 
Did this article resolve the issue?