Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Rank on a Measure

Hello,

 

I am trying to rank a table based on a measure field. 

 

In my table I have 'Region' (North, South, East, West), 'Today's Sales', 'Today's Sales Target', and '% of target achieved' (measure field).

 

I want to rank on the '% of target achieved' but I can't work out how.

 

Please help :)

 

Thanks!

 

 

Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi  Anonymous  ,

     

    If you need to create a measure but not a calculated column for Rank, you can use the DAX below.


    Rank = RANKX(FILTER(ALLSELECTED(Table1),Table1[Region]=MAX(Table1[Region])),Table1[% of target achieved],,ASC)

     

    Best regards
    Amy

5 Replies

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi Anonymous ,

     

    You can create column using DAX below.

     

    Rank = RANKX(FILTER(Table1,Table1[Region]=EARLIER(Table1[Region])),Table1[% of target achieved],,ASC)

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Amy,

       

      Thanks for the message!

       

      When I use the formula you said I get an error regarding the 'Earlier' part of the equation:

       

      "EARLIER/EARLIEST refers to an earlier row contect which doesn't exist".

       

      I've triple checked I've entered it right!

       

      Any suggestions?

       

      Thanks!

      • v-xicai's avatar
        v-xicai
        Community Support
        7 years ago

        Hi Anonymous ,

         

        Did you create measure using the DAX?  You may need to create it in column , EARLIER and EARLIEST are usually used in calculated column. You can go through the link to learn more: https://docs.microsoft.com/zh-cn/dax/earlier-function-dax.

         

        Best Regards,

        Amy

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

         

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    Hi  Anonymous ,

     

    Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

     

    Best regards

    Amy