Forum Discussion
Create New Column with RANKX DAX for ranking by multiple variable
Hi All,
I would like to create new column (Rank) using RANKX, this rank coloumn is group by multiple criteria (Type 1, Type 2, Month, and Year) then rank by the score. below are the sample of my table.
| Type 1 | Type 2 | Month | year | Score | Rank |
| A | AA | JAN | 2023 | 100 | 1 |
| A | AB | JAN | 2023 | 10 | 1 |
| A | AB | FEB | 2023 | 100 | 1 |
| A | AA | FEB | 2023 | 200 | 2 |
| A | AA | FEB | 2023 | 300 | 1 |
| B | BB | MAR | 2023 | 100 | 1 |
| B | BB | MAR | 2023 | 50 | 2 |
| B | BA | FEB | 2023 | 60 | 1 |
| B | BA | FEB | 2023 | 30 | 2 |
| C | CC | FEB | 2023 | 40 | 2 |
| C | CC | FEB | 2023 | 100 | 1 |
| C | CC | JAN | 2023 | 90 | 1 |
| C | CD | MAR | 2023 | 80 | 1 |
| C | CE | MAR | 2023 | 70 | 1 |
| D | DE | JAN | 2023 | 50 | 2 |
| D | DD | JAN | 2023 | 45 | 2 |
| D | DD | JAN | 2023 | 67 | 1 |
| D | DE | JAN | 2023 | 99 | 1 |
anyone have using RANKX function for this use case ? appreciate much of your help.
Rnk = COUNTROWS( FILTER( DATA, DATA[Type 1] = EARLIER( DATA[Type 1] ) && DATA[Type 2] = EARLIER( DATA[Type 2] ) && DATA[year] = EARLIER( DATA[year] ) && DATA[Month] = EARLIER( DATA[Month] ) && DATA[Score] >= EARLIER( DATA[Score] ) ) )
3 Replies
Hi Anonymous
This should work:
sumSC= SUM(Table[Score]) RANX Total= RANKX( ALLEXCEPT('Table','Table'[Type 1],'Table'[Type 2],'Table'[Month],'Table'[year]),[sumSc],,DESC )Rnk = COUNTROWS( FILTER( DATA, DATA[Type 1] = EARLIER( DATA[Type 1] ) && DATA[Type 2] = EARLIER( DATA[Type 2] ) && DATA[year] = EARLIER( DATA[year] ) && DATA[Month] = EARLIER( DATA[Month] ) && DATA[Score] >= EARLIER( DATA[Score] ) ) )
