cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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
Highlighted
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

 

Highlighted

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

Highlighted

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!  

Highlighted

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

 

Highlighted

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





Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors