KNOWLEDGE BASE

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


Published: 05 Feb 2018
Last Modified Date: 05 Feb 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 can be reviewed in the attached packaged workbook.
  1. Create a set based on top N for the desired measure by opening the context menu for the dimension
  2. Drag the dimension onto the Rows shelf, and drag the set onto Color on the Marks card. 
  3. Open the context menu on the dimension pill in the view, and sort it by the desired measure 
  4. Create table calculations for [Last Color] and [Last Row]. [Last Color] should be defined as LAST() = 0, and [Last Row] should be defined as FIRST() = LAST()
  5. Create a [Replacement Measure] calculation, for the measure used to rank by top N (and sort): 
    IF [Last Color] AND [Last Row] 
    THEN WINDOW_SUM(SUM([Original Measure])) 
    ELSE 
    SUM([Original Measure]) 
    END
  6. Place [Replacement Measure] on Text on the Marks card, and edit the table calculations from the pill's context menu, so that [Last Color] is calculated along the set, and [Last Row] is calculated along Table (Down). The main table calculation should be computed along the set. 
  7. Create a [Top N Filter] calculation, as follows (using top 5 as an example): 
    RANK(SUM([Original Measure)) <= 5 AND ATTR([Set Name])
  8. Drag this calculation to the Filters shelf, and filter to include values where the calculation is True. Because we are filtering on a table calculation, this modification to the view occurs only after the Grand Totals are calculated (so that the Grand Totals are preserved). 
Did this article resolve the issue?