How To Keep Top 10 Sub-Category by Total Sales While Showing Percent of Total with Missing Data
Published: 06 Apr 2020 Last Modified Date: 08 Jul 2022
Question
Top 10 data shows more than 10 records when using RANK(TOTAL(SUM([Sales]))) filter due to missing data. This occurs when using [Percent of Total] in Quick Table Calculation.
Environment
Tableau Desktop
Answer
1. Create following calculation
[WINDOW_AVG]
WINDOW_AVG(TOTAL(SUM([Sales])))
[RANK]
RANK(WINDOW_AVG)
2. Drag [RANK] to [Filter]
3. Right click on [RANK] -> [Edit Table Calculation]
Set Nested Calculations as bellow [RANK] : Table (down) [WINDOW_AVG] : Table (across)
4. Right click on [RANK] -> [Edit Filter]
Set Range of Values as 1 ~ 10
Additional Information
Tips: Use WINDOW functions to deal with missing data when needed.
Thank you for providing your feedback on the effectiveness of the article.