Forum Discussion
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 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 =
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.
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])
7 Replies
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])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).
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 , 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])))
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 belowand then you can use this rank column for sorting
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.
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-columnsAlso check measure Rank
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
