Rankx Multiple levels to Hierarchy

[Anonymous]

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

 

[Anonymous · Accepted solution]

Hi @Anonymous,

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

[Anonymous]

Thanks @Anonymous  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])))))