cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## DAX RANK - At different Hierarchy

I have three tables as shown below. I need to find the rank at store,product level based on units sold.Also,in the view I need another rank column whcih displays rank of the product at city,product,Rank1 based on the units sold

Location

 Country SATE CITY Store US TEXAS PLANO 1 US TEXAS PLANO 2 US TEXAS IRVING 3 US TEXAS IRVING 4 US TEXAS DALLAS 5 US TEXAS DALLAS 6

Product

 productcat productsubcat productname C1 SC1 P1 C1 SC1 P2 C1 SC1 P3 C1 SC2 P4 C1 SC2 P5 C1 SC2 P6

FactSales

 Store productname salesunit 1 P1 2 1 P2 3 1 P3 1 1 P4 2 2 P3 5 2 P2 1 2 p1 1 3 P4 2 3 P1 1 3 P2 3 3 P5 1

1 ACCEPTED SOLUTION
Community Support

Hi @deepu2108 ,

Here I create the relationship between tables as below.

Then I create three measures.

`Measure = CALCULATE(SUM(Factsales[salesunit]))`
`ranklocation = IF(ISBLANK([Measure]),BLANK(),RANKX(ALL(Location),[Measure],,DESC,Dense))`
`rankproduct = IF(ISBLANK([Measure]),BLANK(),RANKX(ALL('Product'),[Measure],,DESC,Dense))`

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
3 REPLIES 3
Community Support

Hi @deepu2108 ,

Here I create the relationship between tables as below.

Then I create three measures.

`Measure = CALCULATE(SUM(Factsales[salesunit]))`
`ranklocation = IF(ISBLANK([Measure]),BLANK(),RANKX(ALL(Location),[Measure],,DESC,Dense))`
`rankproduct = IF(ISBLANK([Measure]),BLANK(),RANKX(ALL('Product'),[Measure],,DESC,Dense))`

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Community Support

Hi @deepu2108 ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Post Patron

Im not quite sure about what you want...according to what I understand, you want to create 3 rank measures, right?

If you create a total store units measure, you could do this: (if this work, dont forget to give a like) see ya.

Spoiler
RANK CITY = RANKX(ALL('Stores Details');[Total Sales U];;DESC)
Spoiler
RANK PRODUCT = RANKX(ALL('Products details'[productname]);[Total Sales U];;DESC)
Spoiler
RANK STORE = RANKX(ALL('Stores Details'[Store];'Stores Details'[Store]);[Total Sales U];;DESC)

You will get something like these:

## Helpful resources

Announcements

#### Power BI May 2023 Update

Find out more about the May 2023 update.

#### Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors
Top Kudoed Authors