Forum Discussion

ar-data's avatar
ar-data
Helper II
5 years ago
Solved

Create a rank column based on summary

I have a simple data as followss:   Year Customer Amount Y1 C1 100 Y1 C2 70 Y1 C2 150 Y2 C1 210 Y2 C2 80 Y2 C3 120   Basically, a table which has Year wise d...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi ar-data ,

     

    You can refer to the following calculated column:

    >>1. Now I want to create a rank column and raking should be based on total amount value for a customer for a year

     

    RankbyYear = var a = SUMMARIZE('Table','Table'[Year],'Table'[Customer],"_amount",SUM('Table'[Amount])) var b = ADDCOLUMNS(a,"_rank",RANKX(FILTER(a,'Table'[Year] =EARLIER('Table'[Year])),[_amount])) return SUMX(FILTER(b,'Table'[Year] = EARLIER('Table'[Year])&&'Table'[Customer] = EARLIER('Table'[Customer])),[_rank])

     

    >>2. And another rank column and ranking should be based on total amount value for a customer ignoring year or any other column

     

    Rank = var a = SUMMARIZE('Table','Table'[Customer],"_amount",SUM('Table'[Amount])) var b = ADDCOLUMNS(a,"_rank",RANKX(a,[_amount])) return SUMX(FILTER(b,'Table'[Customer] = EARLIER('Table'[Customer])),[_rank])

     

     

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

     

    Best Regards,

    Dedmon Dai

     

     

sayaliredij's avatar
sayaliredij
Solution Sage
5 years ago

ar-data  - 

 

you can use the following formula (almost similar to you did ) to create a calculated column

Rank =
RANKX(
'Table',
'Table'[Amount]
)
 
It is generated as below

 

and then you can use this rank column for sorting