'Excluding null Value from Top 5 Rank but Including in Total
So this is a one I have been cracking my head because I know there has to be a way.
I have a Top 10 Country column ranked based on sales. Unfortunately in my data source there were null values for countries, no value was defining it, and in order to fix those null values I replaced them with a string Unknown. The idea was to create Other category and everything not in the top 5 sum in there and finally have a total of all the values.
I started with ranking column defining,
Country Sales Rank = RANKX(
ALL (Country[Country Name]),
CALCULATE([Sales], ALLEXCEPT(Country,Country[Country Name])), ,DESC
The problem is when adding the rank to the countries the Unknown sales are top 4.
I also tried categorizing the countires in Other and then grouping them; one group for top 5 another Other group for the rest but the Unknown still remains in the top 4.
Country Top 5=
IF(
Country[Country Sales Rank] IN {1,2,3,4,5}, Country[Country Name], "Others")
Ideally this is what I am going for:
Did anyone come across a similar issue I am having? Thanks!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|