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 :

 
 
 
 
 
 
 
 
 
 

 

1 ACCEPTED SOLUTION

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

14 REPLIES 14
Super User IV
Super User IV

@jlizzul , Measure rank is context-sensitive. You create a Rank on store, It will rank store or store rank inside other group by

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

I modified my pbix, so i can send it. (70Mo)

How can i send it to you ?

You could give me your @ by private message ?

Hi @jlizzul ,

 

Would you please try to use the following rankx measure for it:

RANKX = RANKX(ALL('Stores'[magasin]),CALCULATE(SUM('Daily Sales N'[MNT_TTC])),,DESC)

If it dosen't work, would you please try to show us some sample data just contain these three table by onedrive for business?

 

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

Hi,

 

My problem is still here.

Here are samples of data :

jlizzul_0-1601537038363.png

 

My joins are :

(1) STORES.RK_ETT = SALES.FK_ETT (n)

(1) CALENDAR.DAT_REF = SALES.DAT_VTE (n)

 

If you want the pbix, can you give me your @, so i can give you access to my drive

 

Regards,

Julien

Hi @jlizzul ,

 

Would you please show us sample pbix by onedrive for business?

 

Best Regards,

Dedmon Dai

Hi @jlizzul ,

 

Please check the permission for it.

 

Best Regards,

Dedmon Dai

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

 

Helper I
Helper I

Result.JPGDataModel.JPG

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors