KNOWLEDGE BASE

Displaying a Value, Subtotal, and Grand Total in the Same View


Published: 31 Dec 2013
Last Modified Date: 22 Nov 2016

Question

How to display the value of a dimension member, the dimension subtotal, and grand total 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 "Sales" to the view. 
  2. Drag "Department" the Columns shelf.
  3. Drill down on the Department dimension, so that the it expands to Category.
  4. Drag Region on the Rows shelf.

Step 2 - Create calculated fields

  1. Create a new calculated field to display the Department Profit per Region.
  2. Go to Analysis > Create Calculated Field.
    1. Enter a name, e.g. 'Department 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 under the Measure values list under the Mark cards.
  6. Repeat step 2 to create a calculated field to filter what category will be displayed.
    1. Enter a name, e.g. 'Lookup Category'.
    2. Enter the formula: LOOKUP(MIN([Category]),0)
    3. Click OK.

Step 3 - Adjust the view

  1. In the Measure Values list, move the SUM(Profit) value to the top of the list
  2. In the Rows shelf, move the Measure Names from to the Columns shelf.
  3. In the Measure Values list, right click on "Department 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 the "Lookup Category" calculated field to the Filter shelf, select a dimension and click OK.
  6. Right-click on the filter and select Show Filter.
  7. In the Filter menu, select Single Value (list) or as desired. 

Optional

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

Additional Information

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