cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
perkses Regular Visitor
Regular Visitor

Rankx Multiple levels to Hierarchy

I am trying to rank different stores based on Sales.  These stores are also broken out by Country, Region, District.

 

I would like to be able to rank the countries against each other, the Regions, and the districts.

 

Something like this all in one matrix.  What would be the correct Formula for this?

 

USA 1                                                  RANK 1  

                 US Central                          RANK 2

                                    STORE 1           RANK2

                                    STORE 3           RANK 3

                                    STORE 4            RANK 1  

                 US EAST                              RANK 1

                 US South                            RANK 3

 

China                                                   RANK 3

Canada                                                RANK 2

 

 rankx.png

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Rankx Multiple levels to Hierarchy

Hi @perkses,

 

Nope, you can't direct write a simple formula to achieve multiple level ranking.

 

In my opinion, your formula should nested with multiple ranking formulas based on each level and concatenate by if statement. After these, you can use if statement to check current level and use correspond ranking formula.

 

You can take a look at below is a blog, it told about how to use if statement to check current hierarchy level.

Clever Hierarchy Handling in DAX

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Rankx Multiple levels to Hierarchy

Hi @perkses,

 

Nope, you can't direct write a simple formula to achieve multiple level ranking.

 

In my opinion, your formula should nested with multiple ranking formulas based on each level and concatenate by if statement. After these, you can use if statement to check current level and use correspond ranking formula.

 

You can take a look at below is a blog, it told about how to use if statement to check current hierarchy level.

Clever Hierarchy Handling in DAX

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

View solution in original post

perkses Regular Visitor
Regular Visitor

Re: Rankx Multiple levels to Hierarchy

Thanks @v-shex-msft  Xiaoxin.... That did the trick...

 

Here was my final dax formula.

 

Rank = IF (
ISFILTERED (dim_location_view[StoreDescription]),
rankx(all(dim_location_view[StoreDescription]),[Total Plan])
, IF(
ISFILTERED (dim_location_view[DistrictNumAndDesc]),
rankx(all(dim_location_view[DistrictNumAndDesc]),[Total Plan])
, IF(
ISFILTERED (dim_location_view[RegionNumAndDesc]),
rankx(all(dim_location_view[RegionNumAndDesc]),[Total Plan])
,
IF(
ISFILTERED (dim_location_view[ZoneName]),
rankx(all(dim_location_view[ZoneName]),[Total Plan])))))
 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)