Create a rank column based on summary

[ar-data]

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 data where a customer may have more than one line item with same or different amount value.

1. Now I want to create a rank column and raking should be based on total amount value for a customer for a year
2. And another rank column and ranking should be based on total amount value for a customer ignoring year or any other column

I was able to do this with a simple measure as follows:

1. Rank = 

RANKX(ALL(Info[Customer]), Sum(Info[Amount]))

However, I am not able to do the same using a column. the reason I need to create a column is to sort the legend in the stacked bar chart using rank column.

Thank you.

[v-deddai1-msft · Accepted solution]

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

[v-deddai1-msft · Accepted solution]

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]

@v-deddai1-msft Thank you and it works as expected. If its ok can you please explain the "retrun Sumx(...)" part. I am not able to understand how that is adding a new column to the existing table from the values in summarized table (var a).

[v-deddai1-msft]

Hi @ar-data ,

We need to use aggregation function to get the value by filter function(maxx can also be used in the case).  Usually, we use the below formula to get data :

CALCULATE(SUM(TABLE[VALUE]),FILTER(Table,........));

But in summary table (temporary table )with self-created columns , if we use formula like above. We'll not get the self-created columns because the summary table is coming backward. We can use the sumx or maxx to put summary table forward to get the self-created columns.

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]

@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.

[amitchandak]

@ar-data , In case In missed something on earlier , refer sub category rank Under this topics 

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

Also check measure Rank
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[sayaliredij]

@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

[amitchandak]

@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])))