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 |
