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

     

     

amitchandak's avatar
amitchandak
Super User
5 years ago

ar-data , if need a column, you need to create a column rank like

 

Example
RANKX(Filter(Info,[Year] =earlier[Year]), Info[Amount])

 

 

Measure rank like, But you can not sort column on that

RANKX(Filter(allselected(Info[Year],Info[Customer]),[Year] =Max[Year]), calculate(Sum(Info[Amount])))