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
Sumanth_23
Memorable Member
Memorable Member

Require help with creating 2 levels of Ranks

hi, I am trying to create a rank at 2 level - Region and State per say based on a calculated measure (wages / sales). I have Sales and Wages data at day level and have dimension tables for location - can you please help on how I can achieve this. I have been trying to implement this using RANKX but not giving me the desired output.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



1 ACCEPTED SOLUTION

Hi @Sumanth_23 ,

 

Try the following measures:

 

Rank by District =
IF (
    HASONEVALUE ( Branch[Branch] );
    RANKX ( ALLSELECTED ( Branch[Branch] ); ( [Ratio] );; ASC; SKIP )
)



Rank by Region =
IF (
    HASONEVALUE ( Branch[Branch] );
    RANKX ( ALL ( Branch ); ( CALCULATE ( [Ratio] ) );; ASC; SKIP )
)

 

The branch table is you location table. I'm assuming your Ratio is a measure, altough on the sample table you provide the calculation between wages and sales for district 2 is not matching.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
Sumanth_23
Memorable Member
Memorable Member

Sorry for some reason could not put in the sample data and desired output in the original post - looking to calculate Column G & H in the below screenshot

 

Sumanth_23_0-1600427442952.png

 

Regards,

Sumanth

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



Hi @Sumanth_23 ,

 

Try the following measures:

 

Rank by District =
IF (
    HASONEVALUE ( Branch[Branch] );
    RANKX ( ALLSELECTED ( Branch[Branch] ); ( [Ratio] );; ASC; SKIP )
)



Rank by Region =
IF (
    HASONEVALUE ( Branch[Branch] );
    RANKX ( ALL ( Branch ); ( CALCULATE ( [Ratio] ) );; ASC; SKIP )
)

 

The branch table is you location table. I'm assuming your Ratio is a measure, altough on the sample table you provide the calculation between wages and sales for district 2 is not matching.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



hi @MFelix  - Thanks a lot for the help! I was able to tweak the measures to make it work but I notice that it not behaving as expected. 

 

The District Rank works perfectly but the Region Rank works if it is in a separate table (table on the right - Region + Branch) but when I include it in the table (table on the left - Region + Metro + Branch) then it gives me the same values as the District Rank. 🤔

 

Can you please help me try and figure this one out. 

 

Sumanth_23_0-1600490277429.png

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



Measures are based on context so you need to add the additional level to your rankx in the allselected part 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



hi @MFelix - Thanks that did the trick!! 👍  Was having a bit of a brain breeze moment 😊

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



hi @MFelix - Thank you for your quick response; I shall give this a shot and get back to you.. 

 

Also yes, Ratio is a measure - the mock data got a little messed up. Thanks!  

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!

Proud to be a Super User!



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.