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"
Last Modified Date: 22 Feb 2023
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. Sample workbook can be downloaded from the link right side of this article.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
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
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
4. Move Sub-Category from Rows to Detail. The view is now showing the TOP 2 and "Others" sub-category.
Additional Information
Finding the Top N Within a CategoryThank you for providing your feedback on the effectiveness of the article.
Open new Case
Continue Searching
Knowledge Base
Community
Product Help
Training and Tutorials