Forum Discussion

Nicks612's avatar
Nicks612
Icon for Helper I rankHelper I
2 years ago
Solved

How to generate the RANK with reference of two other columns - dynamic Ranking

hello everyone,   I want to generate the rank column with reference to other columns. Please refer to the below explanation of the problem.   for example, for Sr.No = '123456', there are three D...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Nicks612 ,

     

    You can create a calculated column as follows.

    Column = RANKX(FILTER('Table',[Sr.No]=EARLIER('Table'[Sr.No])),[DBKey],,ASC,Dense)

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Nicks612 ,

     

    Below are the results achieved using power query editor.

     

    1) First, need to group by Sr.No column.

     

    2) Then add a custom column with the M code Table.AddRankColumn([All],"Ranking",{"DBKey"})

    3) Then Expand the custom column by unchecking Sr.No column.

    4) Remove the All Column..

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Nicks612 ,

 

Below are the results achieved using power query editor.

 

1) First, need to group by Sr.No column.

 

2) Then add a custom column with the M code Table.AddRankColumn([All],"Ranking",{"DBKey"})

3) Then Expand the custom column by unchecking Sr.No column.

4) Remove the All Column..

  • Nicks612's avatar
    Nicks612
    Icon for Helper I rankHelper I
    2 years ago

    Thanks for the reply!! I want to know how to change the DBKey column number format to a whole number. I don't want the 2.02305E+13 format. Is there any option to convert it?  I have tried to overcome this issue by using Data Type options but have not succeeded. Please guide me on that.