Forum Discussion
Rank value with duplicate category for each month
HI friends
How can I create a rank column with averaged value for each name with duplicate rows (because of date) like the example below
| Month | Category | Value | Rank column |
| Jan | A | 5 | 2 |
| Jan | A | 7 | 2 |
| Feb | A | 10 | 1 |
| Feb | A | 15 | 1 |
| Jan | B | 4 | 2 |
| Jan | B | 8 | 2 |
| Feb | B | 10 | 1 |
| Feb | B | 12 | 1 |
Hi, Anonymous
Try calculated column as below:
Rankx = VAR tab = SUMMARIZE ( FILTER ( 'Table', 'Table'[Category] = EARLIER ( 'Table'[Category] ) ), 'Table'[Month], 'Table'[Category], 'Table'[Average] ) RETURN RANKX ( tab, 'Table'[Average],, DESC, DENSE )Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Anonymous ,
Avg = rankx(filter( allselected(Table[Category] , Table[Month] ), Table[Category] = max(Table[Category])), calculate(Avgerage(Table[Value])),,desc,dense)
thank you amitchandak for your reply but all returned only a value of 1
- v-easonf-msft
Community Support
Hi, Anonymous
Try calculated column as below:
Rankx = VAR tab = SUMMARIZE ( FILTER ( 'Table', 'Table'[Category] = EARLIER ( 'Table'[Category] ) ), 'Table'[Month], 'Table'[Category], 'Table'[Average] ) RETURN RANKX ( tab, 'Table'[Average],, DESC, DENSE )Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
