RANKX issue

[Anonymous]

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 :

[amitchandak]

@Anonymous , 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/367415

[Anonymous]

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 ?

[v-deddai1-msft]

Hi @Anonymous ,

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

[Anonymous]

Hi,

My problem is still here.

Here are samples of data :

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

[v-deddai1-msft]

Hi @Anonymous ,

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

Best Regards,

Dedmon Dai

[Anonymous]

Can you try this link :

https://myadeo-my.sharepoint.com/:u:/g/personal/julien_lizzul_ext_adeo_com1/EZITrMNvz4NEmL2zGiBZ3AsB...

Pass : RankX_Issue

Regards,

Julien

[v-deddai1-msft]

Hi @Anonymous ,

Please check the permission for it.

Best Regards,

Dedmon Dai

[Anonymous]

hi @v-deddai1-msft 

Please try this one : 

https://myadeo-my.sharepoint.com/:u:/g/personal/julien_lizzul_ext_adeo_com1/EZITrMNvz4NEmL2zGiBZ3AsB...

Password : RankX_Issue

Regards,

Julien

[v-deddai1-msft]

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]

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 · Accepted solution]

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]

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

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

[Anonymous]