Ranking

[dmatliak]

Hello guys, I have an issue I can not move on.

I have a table of clients with contacts to them. Each client can have 1, 2, or multiple contacts. I need a new field let's say "rank" that will assign numbers 1,2,3 and so on based on how many contacts are related to a client. In excel there is similar function RANK which basically doe this, but how can I do it in PBI?

Thanks for help in advance.

[Jihwan_Kim]

Hi, @dmatliak 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

https://www.dropbox.com/s/19rqx3in7aqn86u/dmat.pbix?dl=0 

Contact Count =

DISTINCTCOUNT('Table'[Contact])

Rank by Contact Count =

RANKX(ALL('Table'[Client]), [Contact Count],,DESC)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[dmatliak]

Hi Jihwan_Kim,

thank you for quick reply. But this is not what I am looking for. What I need as an output is this (see. the screenshot). For each customer I want all contacts to be showed but with rank 1,2,3 and so on, depending on how many contacts are for each client.

Thank you for help.

Dusan

[Jihwan_Kim]

Hi, @dmatliak 

Thank you for your feedback.

Sorry that I cannot create the relevant sample pbix file.

Please share your sample pbix file's link here in order to possibly not create any misunderstanding.

Thanks.

[dmatliak]

Hello, here is the link. I just uploaded new table to your template. My wishful state ist to have new column having numbers 1,2,3 for each row within the customer ID (For example C01 will have numbers 1-5 because he has 5 different contacts).

https://www.dropbox.com/s/nzug861ekiq4b97/dmat.pbix?dl=0

[Jihwan_Kim]

Hi, @dmatliak 

Thank you for sharing.

I am not sure still whether I understood your question correctly.

I assume you want to have an index column per each client ID.

I think you are right and using RANKX function is one of many ways to create the index column.

However, by seeing the sample that is given by you, I thought I could not use RANKX, because there were no columns that can be ranked row by row.

So, I had no choice but to use Power Query.

I created a new table, and you can see this table inside the Power Query how the steps are to create the index column.

If you have a column that can be ranked, for instance, date column, then I can try to create it not by Power Query.

https://www.dropbox.com/s/19rqx3in7aqn86u/dmat.pbix?dl=0 

[dmatliak · Accepted solution]

Hello, I finally found the solution here. This is exactly what I was looking for. But thank you for your help trying very quickly.

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query