'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.

Unknown in the top 5

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:

End result

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