Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 =
Solved! Go to 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
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 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
@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-columns
Also check measure Rank
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
@ar-data -
you can use the following formula (almost similar to you did ) to create a calculated column
and then you can use this rank column for sorting
Proud to be a Super User!
@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])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |