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

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  ,

     

    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