Forum Discussion

NickMol25's avatar
NickMol25
Frequent Visitor
5 years ago
Solved

Dynamically display Column values based on RANKX formula.

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")



 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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")
     
     
     
     

    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.

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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's avatar
      NickMol25
      Frequent Visitor
      5 years ago

      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's avatar
        Jihwan_Kim
        Super User
        5 years ago

        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")
         
         
         
         

        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.