Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

RANKX issue

Hi all,   Got an issue with RANKX. Here is my data model :   1 Fact table : Sales 2 dimensions tables : Calendar, stores   I want to create a measure to have store's rank over turnover.   I...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    You can use the following  measure in your table visual:

     

    Rang CA BU Mois Filtre = var a = SUMMARIZE(ALL(Stores),Stores[Magasin],"CA TTC",[CA TTC Mois],"rank",RANKX(ALL(Stores),CALCULATE([CA TTC Mois],ALLEXCEPT(Stores,Stores[Magasin])),,DESC)) return SUMX(FILTER(a,Stores[Magasin] = MAX(Stores[Magasin])),[rank])

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

v-deddai1-msft's avatar
v-deddai1-msft
Community Support
5 years ago

Hi Anonymous ,

 

Is the screenshot below you want?

 

 

Please refer to the measure:

 

Rang CA BU Mois Filtre = if(HASONEVALUE(Stores[Magasin]),rankx(all(Stores[Magasin]),[CA TTC Mois],,DESC),0)

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

This rank works fine in this table without any filter.

But if i try to filter on Bordeaux for example, i need to have the rank over all the stores : it should be 3.

The rank when i filter is 1.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    You can use the following  measure in your table visual:

     

    Rang CA BU Mois Filtre = var a = SUMMARIZE(ALL(Stores),Stores[Magasin],"CA TTC",[CA TTC Mois],"rank",RANKX(ALL(Stores),CALCULATE([CA TTC Mois],ALLEXCEPT(Stores,Stores[Magasin])),,DESC)) return SUMX(FILTER(a,Stores[Magasin] = MAX(Stores[Magasin])),[rank])

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    v-deddai1-msft

    Thank you for your reactivity and your expertise.

     

    I still have to do some tests to make sure everything is ok, but it seems to be working.

     

    Just one question, is it normal to have to apply such a complex formula for a simple rank ?

     

    Thanks again

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    As far as I concerned, Rankx measure the hard part in dax to understand. You can learn more about it by link provided by amitchandak. Maybe it's not the best way, but it's the best way I can write.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai