Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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,R...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    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

christianfcbmx's avatar
christianfcbmx
Post Patron
7 years ago

Hi Anonymous 

 

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: