Rank on a Measure

[Anonymous]

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!

[v-xicai]

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

[v-xicai]

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]

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 · Accepted solution]

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]

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.