Make continuous ranking if values are the same

[Soof1234]

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

[Jihwan_Kim]

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 )
    )
)

[SaiChand]

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

[Soof1234]

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

[Jihwan_Kim · Accepted solution]

Hi,

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

Rank: =
VAR _table =
    ADDCOLUMNS (
        ADDCOLUMNS (
            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]
        )
    )

[Soof1234]

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 = 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

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

[Jihwan_Kim]

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.

[Soof1234]

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:

https://anonfiles.com/b0g9ddT2ya/Dummy_pbix

Thanks in advance!

Kind Regards, Soof1234

[Jihwan_Kim]

Hi,

I cannot open the file in the link. 

[Soof1234]

Hi, 

Excuse me, WEtransfer should do the job. 

https://we.tl/t-Ulz9aUZdzt

Kind Regards,

Soof1234

[Soof1234]

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

Kind Regards,

Soof1234