Forum Discussion
Help With RANKX
I'm very new to PowerBi and am looking for some help with the RANX function. I have a table, 'Result Grades', that needs to grouped as per the table below i.e 'RecNum', 'AcYr', 'Assessment', 'A8 Order' and then ranked by the 'Result Pts' (ascending). I have tried many different ways and the closest I've come to solving the problem is with the formula :-
RankA8 =
RANKX(
FILTER(ALL(
'Result Grades'),
'Result Grades'[RecNum] = EARLIER('Result Grades'[RecNum])&&[A8Order]="1"
),
'Result Grades'[RecNum]&[AcYr]&[Assessment]&[A8Order]&[ResultPts]&[Code]
,,desc
)
The only problem with this formula is it just ranks all the grades for each 'RecNum from 1 to the highest value. Is there any way to achieve the ranking as per the column 'Rank Wanted' as below.
RecNum | AcYr | Assessment | A8Order | Code | Result Pts | RankA8 | Rank Wanted | |
8998 | 2017 | 3 | 1 | aa | 6 | 1 | 1 | |
8998 | 2017 | 3 | 1 | bb | 5 | 2 | 2 | |
8998 | 2017 | 2 | 1 | bb | 3 | 3 | 1 | |
8998 | 2017 | 2 | 1 | aa | 3 | 4 | 2 | |
8998 | 2017 | 1 | 1 | bb | 7 | 5 | 1 | |
8998 | 2017 | 1 | 1 | aa | 7 | 6 | 2 | |
10265 | 2021 | 1 | 1 | bb | 5 | 1 | 1 | |
10265 | 2021 | 1 | 1 | aa | 5 | 2 | 2 | |
10265 | 2020 | 3 | 1 | bb | 5 | 3 | 1 | |
10265 | 2020 | 3 | 1 | aa | 4 | 4 | 2 | |
10265 | 2020 | 2 | 1 | bb | 5 | 5 | 1 | |
10265 | 2020 | 2 | 1 | aa | 4 | 6 | 2 | |
10265 | 2020 | 1 | 1 | bb | 6 | 7 | 1 | |
10265 | 2020 | 1 | 1 | aa | 5 | 8 | 2 | |
10265 | 2019 | 2 | 1 | bb | 5 | 9 | 1 | |
10265 | 2019 | 2 | 1 | aa | 5 | 10 | 2 | |
10265 | 2018 | 3 | 1 | bb | 4 | 11 | 1 | |
10265 | 2018 | 2 | 1 | bb | 3 | 12 | 1 | |
10265 | 2018 | 1 | 1 | En | 3 | 13 | 1 | |
10265 | 2017 | 3 | 1 | En | 3 | 14 | 1 | |
10265 | 2017 | 2 | 1 | En | 4 | 15 | 1 | |
10265 | 2017 | 1 | 1 | En | 4 | 16 | 1 |
UB40 , A new Rank Column
RANKX(
FILTER('Result Grades',
'Result Grades'[RecNum] = EARLIER('Result Grades'[RecNum]) && 'Result Grades'[RecNum] = earlier('Result Grades'[RecNum]) &&
'Result Grades'[AcYr] = earlier('Result Grades'[AcYr]) && 'Result Grades'[Assessment] = earlier('Result Grades'[Assessment]) &&
[A8Order]="1"
),
[ResultPts]
,,desc
)
2 Replies
UB40 , A new Rank Column
RANKX(
FILTER('Result Grades',
'Result Grades'[RecNum] = EARLIER('Result Grades'[RecNum]) && 'Result Grades'[RecNum] = earlier('Result Grades'[RecNum]) &&
'Result Grades'[AcYr] = earlier('Result Grades'[AcYr]) && 'Result Grades'[Assessment] = earlier('Result Grades'[Assessment]) &&
[A8Order]="1"
),
[ResultPts]
,,desc
)Thank you so much for helping. 🙂
