Rank with no duplicates

[sthomas38]

Hi,

I am trying to achieve this ranking (no duplicates) - please see below snapshot. I have to rank each Attribute for around 1000 MSAs. 

I tried adding a random value to the rank - but that is not giving a single digit value between 1 and 11 and giving me tripple digit values. 

Let me know if you have any questions please. Thank you so much for your time and help.

[v-luwang-msft · Accepted solution]

Hi @sthomas38 ,

First add index:

Close and apply,then use the following dax to create a column:

RANK1 = RANKX(FILTER('Table','Table'[MSA]=EARLIER('Table'[MSA])),'Table'[Value_count]+0.000001*'Table'[Index],,DESC,Dense)

Output result:

And you could refer my blog about rank with  same values:

https://community.powerbi.com/t5/Community-Blog/Sort-when-with-same-value/ba-p/2012332 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[sthomas38]

Hi!

Thank you so much for this solution. It helped me! Because of being able to create rank as a column (and nor measure), I was able to achieve so much more (calling out the 3rd rank network and 5th rank network) etc. 

Appreciate your help. Thank you.

[Tahreem24]

@sthomas38 , May this similar post help you out.

https://community.powerbi.com/t5/Desktop/Rank-values-without-duplicate-ranking/m-p/702224

[sthomas38]

Hi Tahreem,

I am not sure if I did it right, or if the solution was not right for this instance - 

This is what I am getting after adding index column in Query Editor and then a RANKX Column. 

VALUE for me is a Calculated Column - in my original dataset, I have IDs thats are distinctly count that created this VALUE column for me. Not sure if that helps. Even to get the RANK column - I had to create it as a Measure and not Column.