Dynamically display Column values based on RANKX formula.

[NickMol25]

I have created some formulas which ranks countries based on a value. I have one measure which shows the rank based on sales, and then a legend column which displays the country when the country is ranked in the top 5, and displays Other if not. 

This works withour filters, but when I do filter, a problem arises. When I only select Europe (for example), the ranking is working fine. It displays the ranking without gaps from Europe. However, the Legend column does not dynamically change upon the change in the ranking. In the legend column, I would like the formula to show the country name of France and Denmark, since they are in the top5 ranking within Europe. 

Is there a way to achieve this? 

Measure = rankx(ALLSELECTED('Countries'),[Sales])

Legend = if([Measure]<=5,'Countries'[Country Name],"Other")

[Jihwan_Kim]

Hi, @NickMol25 

I am not sure if it works, but please try to change your measure like below.

Measure = rankx(ALLSELECTED('Countries'[Country Name]),[Sales])

If allselected is applied to the table level, then I think continent level cannot be filtered.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[NickMol25]

Hey @Jihwan_Kim 

Thanks for the suggestion. The sorting now works as it should, but when I add the legend to my table every value is changed to 1. 

I assume this happens because it is comparing per row and not the total. Any idea how I can make sure that my legend works correctly as well? 

[Jihwan_Kim · Accepted solution]

Hi, @NickMol25 

Thank you for your feedback.

I am not sure why that happened.

Can you change your legend measure like below?

Or, you can also refer to the link down below, that I just created some sample pbix file to test it.

Legend = IF([Measure] <= 5, SELECTEDVALUE(Data[Country]), "Other")

https://www.dropbox.com/s/qs2twf1skurj8kq/test%200330.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[NickMol25]

Hey @Jihwan_Kim 

Works perfectly! Thank you very much. I will accept the answer as solution. 

[Jihwan_Kim]

Thank you very much. 😁

[NickMol25]

One additional question. In a measure everything works fine, but if I want to use this in a graph it would need to be a column. How can I make sure that this will also work? I tried the measure formula but the selectedvalue does not work over there. 

[Jihwan_Kim]

Hi, @NickMol25 

You are correct, and you have to have some kind of grouping table.

For instance, TopNCustomers rank between 1~5, and OthersCustomers rank between 6~100.

Then, in this case, the graph can show the legend.

I hope this helps.

Thank you very much.