KNOWLEDGE BASE

Displaying a Subtotal for Top N with Grand Total for All Data


Published: 05 Feb 2018
Last Modified Date: 19 Sep 2018

Question

How to display a subtotal for top N alongside a grand total for the full dataset.

Environment

  • Tableau Desktop

Answer

The instructions below are steps to achieve the solution demonstrated in the attached packaged workbook.

In this example, we will be calculating the Top 5 Sales by State displaying the Top 5 State's Total Sales and the Total Sales of all States.
Will be referencing the Dimensions and Measures used in the sample workbook but they can be replaced with any desired fields to achieve a similar result.
Note that due to the order of operations of how calculations are computer, steps are sensitive to the sequence they are done so please perform the steps accordingly.
  1. Create a Set based on top N for the desired measure by opening the context menu for the dimension:
    • Right click on [State], select Create > Set.
    • Input desired name, in this case [Set 1].
    • Click on the Top tab > select By Field > specify 5 in the Top value drop down.
    • Select Sales and Sum to indicate what we want to calculate the Top 5 on.
    • Press OK.
  2. Drag [State] onto the Rows shelf, and drag [Set 1] onto Color on the Marks card. 
  3. Next we will change the sorting to display the Top 5 sales at the top of the list:
    • Right click on [State] in the Rows shelf, select Sort.
    • Select Descending under Sort order.
    • Select Field under Sort by and select Sales in the drop down.
    • Select Sum in the Aggregation drop down which should already be defaulted.
    • Press OK.
  4. Create table calculations for [! Last Color]
    • Right click on Measures pane, select Create > Calculated Field.
    • [! Last Color] should be defined as LAST() = 0.
    • Drag [! Last Color] onto Tooltip on the Marks card.
    • Right click [! Last Color] on Marks card and select Edit Table Calculation.
    • Ensure "Compute Using" is set to Specific Dimensions.
    • Ensure In/Out of Set 1 box is checked and State is unchecked.
    • Click on X on the right top corner.
  5. Create table calculations for [! Last Row]
    • Right click on Measures pane, select Create > Calculated Field.
    • [! Last Row] should be defined as LAST() = FIRST().
    • Drag [! Last Row] onto Tooltip on the Marks card.
  6. Create a [Replacement Measure] calculation, for the measure used to rank by top N (and sort):
    • Right click on Measures pane, select Create > Calculated Field and input name [! Sales]
    • IF [! Last Color] AND [! Last Row]
      THEN WINDOW_SUM(SUM([Sales]))
      ELSE
      SUM([Sales])
      END
      
  7. Place [! Sales] on Label on the Marks card.
  8. Edit the table calculations of [! Sales] from context menu on Marks card:
    • Right click on [! Sales] on Marks card.
    • Click Edit Table Calculation
    • Ensure "Compute Using" is set to Specific Dimensions.
    • Ensure In/Out of Set 1 box is checked and State is unchecked.
    • Click on X on the right top corner.
  9. From the main menu, select Analysis > Totals > Show Column Grand Totals to display the grand totals.
  10. Create a Calculated Field named [! Sales Rank] with the formula below.
    RANK(SUM([Sales]))
    
  11. Create a Calculated Field named [! Top 5 Filter] calculation, as follows (using top 5 as an example): 
    [! Sales Rank] <= 5 AND ATTR([Set 1])
    
  12. Drag [! Top 5 Filter] to the Filters shelf and select True to only display the Top 5 State with the highest Sales.
Did this article resolve the issue?