Rank Improvement per multiple groups (calendar year, subject, year level)

[os_ca]

Source data looks below.

Year   YearLevel    Subject Student ID    Mark
2020       5                Eng        1           90     
2020       5                Eng        2           98     
2020       5                Eng        3           88
2020       5                Eng        4           68
2021       6                Eng        1           90     
2021       6                Eng        2           98     
2021       6                Eng        3           88
2021       6                Eng        4           99
2020       5                Math      1           44     
2020       5                Math      2           98     
2020       5                Math     3           88
2020       5                Math     4           68
2020       5                Math    5           67
2021       6                Math    1           99     
2021       6                Math    2           98     
2021       6                Math    3           88
2021       6                Math    4           80
2021       6                Math    5           90

...

in Power BI, I'd like to build a report as shown below. I can build a rank but its ranking is for all subjects and year levels combined. Then I should get top variances (top improvers).

Subject studentiD     2020 Rank        2021 Rank    Rank Variance

Eng           1               2                     3                      -1
Eng            2              1                     2                      -1
Eng           3               3                     4                     -1
Eng           4               4                     1                       3
Math         1               5                    1                       4
Math          2              1                     2                      -1
...

I tried the below code (I got it from somewhere in this forum) but it didn't work. I'd better do all rankings and variance in the source SQL server or it can be done in the Power BI?

RANK by subject =
RANKX (
FILTER (
StudentResults,
StudentResults[Subject] = EARLIER ( StudentResults[Subject])
),
StudentResults[Mark],
,
ASC,
DENSE
)

[v-rongtiep-msft]

Hi @os_ca ,

Please have a try.

Create columns.

C_Rank = RANKX(FILTER('Table','Table'[Year]=EARLIER('Table'[Year])&&'Table'[ Subject]=EARLIER('Table'[ Subject])),'Table'[Mark] )

Nextrank = LOOKUPVALUE('Table'[C_Rank],'Table'[Year],DATEADD ( 'Table'[Year], 1, YEAR ),'Table'[ Subject],'Table'[ Subject],'Table'[Student ID],'Table'[Student ID])

diff = IF(ISBLANK('Table'[Nextrank]),BLANK(), 'Table'[C_Rank]-'Table'[Nextrank] )

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@os_ca , try like this

RANK by subject =
RANKX (
FILTER (
StudentResults,
StudentResults[Subject] = EARLIER ( StudentResults[Subject])
StudentResults[Year] = EARLIER ( StudentResults[Year])
),
StudentResults[Mark],
,
ASC,
DENSE
)

[os_ca]

I had a go with your solution but Filter doesn't have two filter expressions. I tried it with && between the conditions but its results didn't look ok to me either.

StudentResults[Subject] = EARLIER ( StudentResults[Subject]) &&
StudentResults[Year] = EARLIER ( StudentResults[Year])

[smpa01]

@os_ca  you can calulate rank and corresponding variance like this

_rank2020 = 
RANKX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Subject] = MAX ( 'Table'[Subject] )
    ),
    CALCULATE (
        MAX ( 'Table'[Mark] ),
        FILTER ( VALUES ( 'Table'[Year] ), 'Table'[Year] = 2020 )
    ),
    ,
    DESC
)

_rank2021 = 
RANKX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Subject] = MAX ( 'Table'[Subject] )
    ),
    CALCULATE (
        MAX ( 'Table'[Mark] ),
        FILTER ( VALUES ( 'Table'[Year] ), 'Table'[Year] = 2021 )
    ),
    ,
    DESC
)
Var = [_rank2020]-[_rank2021]

pbix is attached

[os_ca]

Does your Dax work for multiple years too? Sorry, I put only two years in the sample but it could be up to 12 years from year 1 to year 12 or year 10 to year 12. Also I would like to build a dynamic rank variance. eg. This year I need a report for the last 6 years from 2016. Next year from 2017 to 2022, etc.