Ranking by Month & Year not working with RANKX

DAX DAX Calculations
1

Dear Experts

May I get a favor from you that I find something strange today that if we want to rank by month & year, our RANKX formula didn’t work out.

However, these RANKX are perfectly fine with getting rank by customers, products, states or other parameters.

Ranking by month & year
Ranking by month & year1891×1018 115 KB

Would you kindly figure out why it is like that in Month & Year.

Thank you so much.
Ranking.pbix (651.2 KB)

1 Like
2

Hello @KhinKyeSin,

Thank You for posting your query onto the Forum.

Well, this was a really very super interesting problem which has been posted onto the Forum. The reason why this problem occured is because a “Sort Order” has been applied on the “MonthInCalendar” field. That is, “MonthInCalendar” field is being sorted by the field “MonthnYear” and due to this an unexpected behaviour was witnessed in ranking.

In order to achieve the results in this type of scenario. Below is the measure alongwith the screenshot of the final results provided for the reference -

Ranking - Harsh =
IF( ISBLANK( [Total Sales] ) ,
    BLANK() ,
    IF( HASONEVALUE( 'Calendar Master'[MonthInCalendar] ) ,
        RANKX(
            ALL( 'Calendar Master'[MonthInCalendar] , 'Calendar Master'[MonthnYear] ) ,
            [Total Sales] , ,
            DESC ) ) )

Final Results
Final Results614×996 26.5 KB

I’m also attaching the working of the PBIX file alongwith the link of an article on “Ranking” topic for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Ranking - Harsh.pbix (655.2 KB)

3 Likes
3

Thank you so much, Harsh.

Really really thank you.

Best Wishes
Khin Kye Sin

4

Hello @KhinKyeSin,

You’re Welcome!!! :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh