KNOWLEDGE BASE

Showing the Top N While Grouping Remaining Values Together


Published: 10 Jun 2014
Last Modified Date: 02 May 2016

Question

How to show the Top N rows while also grouping all remaining values under another header.

Environment

Tableau Desktop

Answer

The following instructions can be reviewed in the workbook attached below.

Create the Parameters

  1. Right-click in the Data pane and then select Create Parameter.
  2. In the Create Parameter dialog box:
  • Name the parameter Top X Choice.
  • For Data Type select Integer.
  • For Allowable values select Range.
  • In Range of values set Minimum to 1, Maximum to 50, and Step size to 1.
  • Click OK.
  1. Right-click the parameter and select Show Parameter Control.
  2. Right-click in the Data window and then select Create Parameter.
  3. In the Create Parameter dialog box:
  • Name the parameter Show All Items.
  • For Data Type select Boolean.
  • In Aliases set True to Show All Items and False to Condense Others.
  • Click OK.
  1. Right-click the parameter and select Show Parameter Control.

Create the Calculated Fields

  1. Select Analysis > Create Calculated Field.
  2. In the Create Calculated Field dialog box, name the field Index.
  3. In the formula field, enter the following formula: INDEX().
  4. Click OK.
  5. Select Analysis > Create Calculated Field.
  6. In the Create Calculated Field dialog box, name the field All vs Summary.
  7. In the formula field, enter the following formula:
    [Index] <= [Top X Choice]
    OR
    [Index] = [Top X Choice] + 1
    OR
    [Show All Items]
  8. Click OK.
  9. Select Analysis > Create Calculated Field.
  10. In the Create Calculated Field dialog box, name the field Item Header.
  11. In the formula field, enter the following formula:
    IF [Index] <= [Top X Choice]
    THEN MIN([Item])
    ELSEIF [Index] > [Top X Choice] AND [Show All Items] = TRUE
    THEN MIN([Item])
    ELSE "Other"
    END
  12. Click OK.
  13. Select Analysis > Create Calculated Field.
  14. In the Create Calculated Field dialog box, name the field Percent of Total.
  15. In the formula field, enter the following formula: SUM([Sales]) / TOTAL(SUM([Sales]))
  16. Click OK.
  17. Select Analysis > Create Calculated Field.
  18. In the Create Calculated Field dialog box, name the field Label for Top X vs Others (% of Total).
  19. In the formula field, enter the following formula:
    IF [Index] <= [Top X Choice]
    THEN [Percent of Total]
    ELSEIF [Show All Items]
    THEN [Percent of Total]
    ELSEIF [Index] = [Top X Choice] + 1
    THEN WINDOW_SUM([Percent of Total], 0, LAST())
    ELSE NULL
    END
  20. Click OK.
  21. Select Analysis > Create Calculated Field.
  22. In the Create Calculated Field dialog box, name the field Label for Top X vs Others (SUM of Sales).
  23. In the formula field, enter the following formula:
    IF [Index] <= [Top X Choice]
    THEN SUM([Sales])
    ELSEIF [Show All Items]
    THEN SUM([Sales])
    ELSEIF [Index] = [Top X Choice] + 1
    THEN WINDOW_SUM(SUM([Sales]), 0, LAST())
    ELSE NULL
    END
  24. Click OK.
  25. Select Analysis > Create Calculated Field.
  26. In the Create Calculated Field dialog box, name the field Sorting.
  27. In the formula field, enter the following formula:
    IF [Index] <= [Top X Choice]
    THEN 1
    ELSE 2
    END
  28. Click OK.
  29. Select Analysis > Create Calculated Field.
  30. In the Create Calculated Field dialog box, name the field Top X?.
  31. In the formula field, enter the following formula:
    IF [Index] <= [Top X Choice]
    THEN "Top " + STR([Top X Choice])
    ELSE STR(SIZE() - [Top X Choice]) + " others"
    END
  32. Click OK.

Create the View

  1. Drag Sorting to Rows.
  2. Drag Top X? to Rows to the right of Sorting.
  3. Drag Item to Rows to the right of Top X?.
  4. Drag Item Header to Rows to the right of Item.
  5. Right-click Sorting on Rows and select Sort.
  6. Select Manual and order the data as 1, 2, and click OK.
  7. Right-click Item on Rows and select Sort.
  8. Set Sort order as Descending, Sort by as Field > Sales > Sum, and click OK.
  9. Right-click Sorting on Rows and clear the Show Header selection.
  10. Right-click Item on Rows and clear the Show Header selection.
  11. Drag Label for Top X vs Others (SUM of Sales) to Columns.
  12. Drag Label for Top X vs Others (% of Total) to Columns.
  13. Drag All vs Summary to Filters, select True, and click OK.
Did this article resolve the issue?