Forum Discussion

ABMN's avatar
ABMN
Helper I
3 years ago
Solved

Breaking ties in RANKX

I'm using the rank function and would like to basically assign a row number insted of a rank, to give a distinct rank instead of duplicate rankings for a tie.

 

I currently have

 

Department          Count            Rank        What I would like for the rank

A                           11                  1              1

B                           10                  2              2

C                           10                  2              3

D                           5                   4              4

E                            3                   5              5

F                            3                   5              6

 

Any help is appreciated.  

6 Replies

    • andrew_k's avatar
      andrew_k
      Helper I

      After searching the internet for a few hours this solved my problem. Thanks.

      Any chance you can explain what it's doing?

  • ABMN check at the bottom of this blog post (section called breaking ties) and see if that helps How to use RANKX in DAX (Part 2 of 3 – Calculated Measures) - RADACAD

     

     

    Follow us on LinkedIn and  to our YouTube channel

    I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

     

    • ABMN's avatar
      ABMN
      Helper I

      parry2k That is still giving me the duplicate ranks.  Here is what I have.

       

      Rank = RANKX(ALLSELECTED('servicedesk BIA_tickets'[department]),CALCULATE(COUNT('servicedesk BIA_tickets'[request_number])
      + INT(CALCULATE(MIN('servicedesk BIA_tickets'[creation_date])/100000))
      ),,DESC)
  • andrew_k it is further ranking the department , dividing it by 100 to add decimal value to the count [CNT] measure  to break the ties.