Rankx

[Wiene24]

Hi,

I have a little problem with my RANKX.

Two tables are involved: Costumor and Order

I have a measure to calculate the total amount of the orders for each costumor:

Omzet = SUM('dba Dossier'[DBCalcTotaalVerkoopTransport])

Now if I want to rank all the costumors, they all give me value 1.

I have this measure to calculate the rank:

Omzetplaats = RANKX(ALL('dba Relatie'[DebiteurNummer]);[Omzet])

DebiteurNummer and RelatieTelefoon are columns in the dba relatie table.

When I use calculated columns it works, but I don't want calculated columns because then I can't work with my slicers.

Later I want to do more ranks so I can get an average rank, but I only want to use the top 100 costumors based on the total sales.

Hope someone can help me, I've been trying to sort it out for a while now but nothing seems to work with measures.

Kind regards,

Tim Wijnen

[v-lili6-msft]

HI, @Wiene24 

Just try this formula:

Omzetplaats 2 = RANKX(ALL('dba Relatie'),[OMZET] )

or

Omzetplaats 3 = RANKX(ALLSELECTED('dba Relatie'),[OMZET] )

Result:

For your formula, if you just drag [DebiteurNummer] into visual, it will work too.

Best Regards,

Lin

[Wiene24]

It's getting closer, but not working completely yet!

I'm using this formula now: 

Omzetplaats = RANKX(ALL('dba Relatie');[Omzet];;DESC)

I want the rank to change if I change my slicers, that's why I didn't use ALLSELECTED.

But no idea why it leaves gaps now and why some are ranked as equals.

Kind regards,

Tim Wijnen

[Anonymous]

Hi @Wiene24 ,

I am curious to know if the Ranks which shows equals have the same Debit number. this could be the reason it is causing you the equal ranks.

Also try the solution provided by Lin @v-lili6-msft , looking at her results , looks like thats what you may be looking for.

Thanks,

Tejaswi

[v-lili6-msft]

hi, @Wiene24 

If you want the rank to change if you change my slicers, please use ALLSELECTED instead of ALL.

don't use Omzetplaats = RANKX(ALL('dba Relatie');[Omzet];;DESC)

If you still have problem, please share a simple sample pbix file.

Best Regards,

Lin

[Wiene24]

@v-lili6-msft @Anonymous ALLSELECTED gave me the same result as ALL

All the debtornumbers are unique so that can't be the problem either.

How do I add a PBIX file? I don't see the paperclip sign to add it.

[v-lili6-msft]

hi, @Wiene24 

You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[Wiene24]

@v-lili6-msft 

https://1drv.ms/u/s!Ai5Ef6OytEAokQAFCb1gGi6a_0Y7?e=lIYj5C

[Anonymous]

Hi @Wiene24 ,

Try this mesaure:

Omzetplaats = 

                     RANKX(

                                 ALL('dba Relatie'[DebiteurNummer]),

                                   [OMZET],

                                    ,ASC

                                  )

Thanks,

Tejaswi