KNOWLEDGE BASE

Aggregate the Other Sub-Categories Other Than the Top N Within Each Category


Published: 06 Jan 2023
Last Modified Date: 09 Jan 2023

Question

How to show the aggregated Sales of other sub-categories other than the TOP N  within each category.

For example, when showing the TOP 2 for each category, how to aggregate other sub-categories as "others"
User-added image

Environment

  • Tableau Desktop

Answer

Use WINDOW_SUM to aggregate the other sub-categories and apply a table filter as demonstrated in the attached packaged workbook and the below steps.

1. Make a [! Sub-category_RANK] calculation to specify the TOP N sub-category and  an "Others".  Set the table calculation to compute using Sub-Category.
IF RANK(SUM([Sales]))<=[TOP N] THEN MIN([Sub-Category])
ELSE "Others"
END
User-added image

2. Make a [! Sales_RANK] calculation to aggregate the values for TOP N and the "Others".  Set the table calculation to compute using Sub-Category.
IF RANK(SUM([Sales]))<=[TOP N] THEN SUM([Sales])
ELSE WINDOW_SUM(IF RANK(SUM([Sales]))>[TOP N] THEN SUM([Sales]) END)
END
User-added image

3. Make a filter as noted below and select TRUE.  Add to the Filters shelf and set the table calculation to compute using Sub-Category.
RANK(SUM([Sales]))<=[TOP N]+1
User-added image

4. Move Sub-Category from Rows to Detail.  The view is now showing the TOP 2 and "Others" sub-category.
User-added image

 
Did this article resolve the issue?