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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kopek
Helper IV
Helper IV

Ranking for separate category issue

Hi!

I am trying to create a calculated column with a ranking for a values, but without any success.

The issue is that i need to create a separate ranking for each bucket (combination of unique values) within one table.

I was able to create a proper rating for entire table, but struggling with splittig that to a bucket.

The bucket for rating should be: ID, Qtr+Yr and Category.
So for each combination of unique ID, Qtr+Yr and Category value should be ranked separately, like below:

IDQtr+YrCategoryValueRank 
123Q1 2020Cat 110003
123Q1 2020Cat 115002
123Q1 2020Cat 120001

 

IDQtr+YrCategoryValueRank
123Q1 2020Cat 225001

 

IDQtr+YrCategoryValueRank
123Q4 2019Cat 110003
123Q4 2019Cat 114002
123Q4 2019Cat 115001

 

IDQtr+YrCategoryValueRank expected
123Q4 2019Cat 223452
123Q4 2019Cat 234561

 

Below you have a high level example of my data :

 

IDQtr+YrCategoryValueRank expectedRank achieved 
123Q4 2019Cat 3777714
123Q4 2019Cat 2345617
123Q1 2020Cat 2250018
123Q4 2019Cat 2234529
123Q1 2020Cat 12000110
123Q1 2020Cat 11500211
123Q4 2019Cat 11500112
123Q4 2019Cat 11400213
123Q1 2020Cat 11000316
123Q4 2019Cat 11000317
345Q1 2020Cat 19999911
345Q4 2019Cat 11111112
345Q1 2020Cat 1888823
345Q1 2020Cat 1777735
345Q1 2020Cat 2555516
345Q4 2019Cat 11113214
345Q4 2019Cat 11112315
345Q4 2019Cat 245118
345Q4 2019Cat 222219
345Q4 2019Cat 36120

 

Any suggestions are appreciated!

 

Thanks!

 

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

For fun only,

DAX measure solution,

CNENFRNL_0-1653580172282.png

 

DAX calculated column solution,

CNENFRNL_1-1653580233778.png

 

PQ solution,

CNENFRNL_4-1653581728668.png

 

Excel worksheet formula is powerful enough,

CNENFRNL_2-1653580334689.png

 

SQL solution,

CNENFRNL_3-1653580832946.png

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

1 REPLY 1
CNENFRNL
Community Champion
Community Champion

For fun only,

DAX measure solution,

CNENFRNL_0-1653580172282.png

 

DAX calculated column solution,

CNENFRNL_1-1653580233778.png

 

PQ solution,

CNENFRNL_4-1653581728668.png

 

Excel worksheet formula is powerful enough,

CNENFRNL_2-1653580334689.png

 

SQL solution,

CNENFRNL_3-1653580832946.png

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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