cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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 created measure Indicateurs[CA TTC] : SUM('Daily Sales N'[MNT_TTC]) => Sum(TurnOver)

And finally my RANKX = rankx(all(Stores),Indicateurs[CA TTC],,DESC)
 

Here is the result :

 
 
 
 
 
 
 
 
 
 

 

14 REPLIES 14

Hi @jlizzul ,

 

Is the screenshot below you want?

 

Capture2.PNG

 

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

 

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.

Hi @jlizzul ,

 

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])

 

Capture.PNG

 

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

View solution in original post

@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

Hi @jlizzul ,

 

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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors