Forum Discussion

Sascha's avatar
Sascha
Regular Visitor
9 years ago
Solved

ranking duplicates

Hi,   I'm looking for a way to 'rank duplicates'. Basically in a simplified way I have data per customer for differen periods, and I want to rank data based on a combination of this. I can best ex...
Need Help
  • ImkeF's avatar
    ImkeF
    9 years ago

    Good points! Giving me the idea of a nice DAX-only solution, that would work in PP as well (Unique row identifier is essential here) ;-)

     

    RANKX(FILTER(table1; table1[Code]=EARLIER(table1[Code]));[Unique ID];;1)

     

Baskar's avatar
Baskar
Resident Rockstar
9 years ago

Cool , I have created one calculated column for this , it is working for me.

 

But u have to create one Index column on this using Power Query. pls look the attachment 

 

 

 

let me know if any help 

Sascha's avatar
Sascha
Regular Visitor
9 years ago

Nice, good to see it's possible. However I'm still struggeling. I have to do this in Powerpivot and not sure I can use the provided solutions.

 

I've tried:

Count=calculate(countrows(table1); allexcept(table1; table1[code]))

Rank=RANKX(filter(ALL(table1[Code]);[Client no.]);[Code])

 

ID   Client no.  P      Code     Count   Rank    Desired Rank

345    100    Q1      100Q1          1         1         1

346    100    Q2      100Q2          1         2         1

347    101    Q1      101Q1          2         3         1

348    101    Q1      101Q1          2         3         2

349    102    Q1      102Q1          1         4         1

350    102    Q2      102Q2          2         5         1

351    102    Q2      102Q2          2         5         2

352    103    Q1      103Q1          1         6         1

353    104    Q1      104Q1          3         7         1

354    104    Q1      104Q1          3         7         2

355    104    Q1      104Q1          3         7         3

356    104    Q2      104Q2          1         8         1

  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

    Good points! Giving me the idea of a nice DAX-only solution, that would work in PP as well (Unique row identifier is essential here) ;-)

     

    RANKX(FILTER(table1; table1[Code]=EARLIER(table1[Code]));[Unique ID];;1)

     

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi Sascha,

       

      Thanks for the points, please mark the corresponding reply as answer for help more people.


      Best Regards,
      Angelia