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

1 ACCEPTED SOLUTION

Accepted Solutions
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
2 REPLIES 2
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
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])))))

Announcements

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

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

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)