Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
EricShahi
Helper IV
Helper IV

RankX in DAX to get the correct ranking for month and year - Required Dynamic ranking

Can someone help , I need a calcuated measure to return Corrected Rank below by only using year month numeric column. 


Year YearMonthNumeric CorrectedRank
2022 202210 1
2022 202211 2
2022 202212 3
2023 202301 4
2023 202302 5
2023 202303 6
2023 202304 7
2023 202305 8
2023 202306 9
2023 202307 10
2023 202308 11
2023 202309 12



1 ACCEPTED SOLUTION

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1707221418165.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

Write these measures

YMN = SUM(Data[YearMonthNumeric])
Rank = RANK(DENSE,GENERATE(ALL(Data[YearMonthNumeric]),all(Data[Year])),orderby([YMN],ASC))-1

My rank measure is not optimal.  There should not be a need to subtract 1.  May be someone can identify my mistake and correct the measure.

Ashish_Mathur_0-1706838880597.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hey Ashish, thanks for your reply. 

Basically I want to caclaute a ranking based on another ranking column.

e.g. I have this ranking  as a calculated column based on the date, now i want to create a dynamic

measure which does a ranking based on the existing ranking column.... 

DateRanking
01/12/2023 00:00  37
01/01/2024 00:00  38
01/02/2024 00:00 39
01/03/2024 00:00 40
01/04/2024 00:00 41
01/05/2024 00:00 42
01/06/2024 00:00 43
01/07/2024 00:00 44
01/08/2024 00:00 45
01/09/2024 00:00 46
01/10/2024 00:00 47
01/11/2024 00:00 48

 

the table should look like below , 

DateRankingRanking_II
01/12/2023 00:00 371
01/01/2024 00:00 382
01/02/2024 00:00 393
01/03/2024 00:00 404
01/04/2024 00:00 415
01/05/2024 00:00 426
01/06/2024 00:00 437
01/07/2024 00:00 448
01/08/2024 00:00 459
01/09/2024 00:00 4610
01/10/2024 00:00 4711
01/11/2024 00:004812

Please try to apply the solution which i shared with you earlier to this new dataset.  DIY.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/


Your Dax Measure list all rows ranking as 1,  like i said on my previous comment ...I want to create a ranking based on the "Ranking" column below...

EricShahi_0-1707214921099.png

 

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@v-weiyan1-msft Please see the above reply and thanks for your help

 

Here is the table , 
below is the measures in DAX : 

YMN = SUM('Table'[YearMonthNumeric])
Rank = RANK(DENSE,GENERATE(ALL('Table'[YearMonthNumeric]),all('Table'[Year])),orderby([YMN],ASC))-1


You can import this as a table 
Year     Date                           Ranking  YearMonthNumeric

202301/11/2023 00:00:0036202311
202301/12/2023 00:00:0037202312
202401/01/2024 00:00:0038202401
202401/02/2024 00:00:0039202402
202401/03/2024 00:00:0040202403
202401/04/2024 00:00:0041202404
202401/05/2024 00:00:0042202405
202401/06/2024 00:00:0043202406
202401/07/2024 00:00:0044202407
202401/08/2024 00:00:0045202408
202401/09/2024 00:00:0046202409
202401/10/2024 00:00:0047202410


I get this , all 1 for rank , 

EricShahi_0-1707219966080.png

 

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1707221418165.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-weiyan1-msft
Community Support
Community Support

Hi @EricShahi ,

 

Based on your description,the data type of the YearMonthNumeric column is numeric.
You might consider using the following code to create Calculated Column.

Corrected Rank = 
RANKX ( ALL ( 'Table' ), 'Table'[YearMonthNumeric],, ASC, DENSE )

Result is as below.

vweiyan1msft_0-1706837590087.png

Please correct me if I misunderstood your needs.


Best Regards,
Yulia Yan

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

EricShahi
Helper IV
Helper IV

EricShahi_0-1706827223540.png

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.