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

     

     

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

sayaliredij amitchandak Thank you for the response. 

 

The ranking has to be assigned after aggregating the Amount values for a particular Customer as there are more than one line item for each customer in each year. So I need something that iterates and then assigns rank. I tried 'Earlier' but somehow that's not working.