Forum Discussion

Soof1234's avatar
Soof1234
Helper I
3 years ago
Solved

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

 

Need Help
Show and Tell
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

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

     

10 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

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

     

    • Soof1234's avatar
      Soof1234
      Helper I
      3 years ago

      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:
       
      RankSourcers.TitleIntakes
      1Kevin3
      1Jane3
      3Joy2
      4Rob1


       
      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's avatar
        Jihwan_Kim
        Super User
        3 years ago

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

         

    • SaiChand's avatar
      SaiChand
      New Member
      3 years ago

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