cancel
Showing results for
Did you mean:
Highlighted
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
Super User III

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

Proud to be a Super User!

Check out my blog:

Power BI em Português

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

Regards,

Sumanth

Highlighted
Super User III

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

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Memorable Member

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

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. 🤔

Highlighted
Super User III

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português

Highlighted
Memorable Member

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

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