cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Make continuous ranking if values are the same

Hi,

Well known issue, but the related posts mostly contain solutions based on a column by adding an index. My problem however, is that I created the ranking based on a measure.

I created a rankingmeasure based on a count measure, see below:

Intakes = COUNT(Intakes[Id])

Ranking =
RANKX( ALL(Intakes[Sourcers.Title]),[Intakes])

Column name in tablevisual below is from a column in a table
Intakes[Sourcers.Title]:

In below table I want to see the rank like 5,6,7,8 instead of 5,5,5,8. Even if the amount of intakes is the same.

Any suggestions on how to solve this. Tried to check related topics but these mostly contains rankings based on columns instead of measures. Thanks in advance!

Kind Regards,

Soof1234

1 ACCEPTED SOLUTION
Super User

Hi,

Thank you for your message, and please check the below picture and the attached pbix file.

``````Rank: =
VAR _table =
ALL ( Intakes[Sourcers.Title] ),
"@rankone", CALCULATE ( RANKX ( ALL ( Intakes[Sourcers.Title] ), [Intakes:],, DESC ) ),
"@ranktwo",
CALCULATE (
RANKX (
ALL ( Intakes[Sourcers.Title] ),
CALCULATE ( MAX ( Intakes[Sourcers.Title] ) ),
,
ASC
)
)
),
"@newindex",
[@rankone] * 100 + [@ranktwo]
)
VAR _newtable =
ADDCOLUMNS ( _table, "@newrank", RANKX ( _table, [@newindex],, ASC ) )
RETURN
IF (
HASONEVALUE ( Intakes[Sourcers.Title] ),
MAXX (
FILTER ( _newtable, Intakes[Sourcers.Title] = MAX ( Intakes[Sourcers.Title] ) ),
[@newrank]
)
)
``````

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

10 REPLIES 10
Super User

Hi,

I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your data model.

``````Rank expected result: =
COUNTROWS (
WINDOW (
1,
ABS,
0,
REL,
SUMMARIZE ( ALL ( Data ), Data[Name], Data[Qty] ),
ORDERBY ( Data[Qty], DESC, Data[Name], ASC )
)
)
``````

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

New Member

Greate Job Jihwan_Kim,
It worked for me, really appreciate your work.

Helper I

Hi @Jihwan_Kim ,

Didnt work unfortunately. I cant upload a file here because of user level probably.

My table looks like this

 Sourcers.Title Kevin Jane Kevin Jane Rob Joy Joy Kevin Jane

Intakes = COUNT(Intakes[Sourcers.Title])
Ranking = RANKX( ALL(Intakes[Sourcers.Title]),[Intakes])

Based on these measures, the tablevisual would look like this:

 Rank Sourcers.Title Intakes 1 Kevin 3 1 Jane 3 3 Joy 2 4 Rob 1

What i want to achieve is that either jane or kevin gets ranking 2, this could be based on alphabetical order for example. Hope this is clear enough for you.

Kind Regards,

Soof1234
Super User

Hi,

Thank you for your message, and please check the below picture and the attached pbix file.

``````Rank: =
VAR _table =
ALL ( Intakes[Sourcers.Title] ),
"@rankone", CALCULATE ( RANKX ( ALL ( Intakes[Sourcers.Title] ), [Intakes:],, DESC ) ),
"@ranktwo",
CALCULATE (
RANKX (
ALL ( Intakes[Sourcers.Title] ),
CALCULATE ( MAX ( Intakes[Sourcers.Title] ) ),
,
ASC
)
)
),
"@newindex",
[@rankone] * 100 + [@ranktwo]
)
VAR _newtable =
ADDCOLUMNS ( _table, "@newrank", RANKX ( _table, [@newindex],, ASC ) )
RETURN
IF (
HASONEVALUE ( Intakes[Sourcers.Title] ),
MAXX (
FILTER ( _newtable, Intakes[Sourcers.Title] = MAX ( Intakes[Sourcers.Title] ) ),
[@newrank]
)
)
``````

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

Helper I

Hi @Jihwan_Kim ,

Hope you are able to resolve this one as well. I added an image from an URL to a separate table with all names of the employees and converted that to type imageURL. It displays the correct image to the belonging person, however it breaks the ranking. I tried to add the image as @columnthree in your measure, but it doesnt work properly.

My data still looks like this

 Sourcers.Title Kevin Jane Kevin Jane Rob Joy Joy Kevin Jane

Intakes = COUNT(Intakes[Sourcers.Title])
Ranking = RANKXALL(Intakes[Sourcers.Title]),[Intakes])

Based on these measures, the tablevisual would look like this:

 Rank Sourcers.Title Intakes 1 Kevin 3 1 Jane 3 3 Joy 2 4 Rob 1

I have a separate table with a relationship 1:* with Sourcers.title -> Medewerkers.Title

 Medewerkers.Title ImageURL Jane www.jane.com/test Rob www.rob.com/test Joy www.joy.com/testtest

You can see that the ranking is 1 for everyone below but it should be the same as in your previous solution. Tried to add a @columnthree to the measure you gave me, but couldnt get it correct.

Hope you got the solution for this as well, as it would finalize my report. Thanks in advance!

Kind Regards,

Soof1234

Super User

Hi,

Please share your sample pbix file's link, and then I can try to look into it to come up with a solution.

If you add a column to the table, in most cases, datamodel is considered as different. Which means, in some cases, measures has to be fixed to show expected results.

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

Helper I

Hi,

Thanks for the help. I cannot upload a file here, but uploaded it on a filesharing website. I used dummy data but with exactly the same column names. I added a third ADDCOLUMN in the measure you supplied but couldnt retrieve the correct rank.

This is the part I added:

,"@rankthree",
CALCULATE (
RANKX (
ALL ( Medewerkers[ProfilePic.Url]),
CALCULATE ( MAX ( Medewerkers[ProfilePic.Url] ) ),
,
ASC))),

The link to the dummy file:

Kind Regards, Soof1234
Super User

Hi,

I cannot open the file in the link.

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

Helper I

Hi,

Excuse me, WEtransfer should do the job.

https://we.tl/t-Ulz9aUZdzt

Kind Regards,

Soof1234

Helper I

Thanks a lot! This works perfectly, also tested it with a lot of equal intakes! See below:

Kind Regards,

Soof1234