Ranking the average of a scaled mark

[dphillips]

Having a few issues with a report I am creating. I have some data for marks for students from various assessments from 2018 and 2019. I want to try and create a report using cards to give an overview of a students average marks from one year compared to the previous year to see if there has been an improvement or not. I have created the average scaled mark formula and also the rank formula (with help from the forum). See screen shot below. If I select 2019 as the fileyear, the Scld Mark Rank is 16 for this student (ID = 113048). If I select 2018 then the Scld Mark Rank is 17 which is what I would expect. In the end, I don't want the table in the report, just the cards. However, the Scld Mark Rank Prev Year is wrong. When I select 2019, I would expect this to be 17 - the rank you see in the table. However, the rank from the previous year in the card is 29. Not sure why this is the case.

Here is the Scaled Mark Rank Prev Year measure - It is the same as the Scld Mark Rank measure except that I subtract 1 from the fileyear and the studentyearlevel to pick up the previous years results. 

Scld Mark Rank Prev Year = 
if(
    not(
        ISBLANK([Scld Mark Ave])),
RANKX( 
    FILTER( 
        ALL( 'Ave Scaled Marks'[FileYear],'Ave Scaled Marks'[StudentYearLevel],'Ave Scaled Marks'[StudentID]),
        'Ave Scaled Marks'[FileYear] = MAX('Ave Scaled Marks'[FileYear]) - 1 && 
        'Ave Scaled Marks'[StudentYearLevel] = MAX( 'Ave Scaled Marks'[StudentYearLevel] ) - 1
    ), 
    [Scld Mark Ave], , DESC
),
BLANK())

Here is a screen shot showing what the ranks should be for 2019 and 2018. 16th and 17th respectively.

Here is a link to the pbix file.

Scaled Mark Data

If you could help me to understand why the previous year card is not showing the correct info and also how to correct that, I would be most appreciative. In the end I want the user to select the fileyear and the studentID and the 2 cards to show me the latest year rank and the previous year rank.

[v-juanli-msft · Accepted solution]

Hi @dphillips 

Create a table, leave this table no related with others.

year table = VALUES('Ave Scaled Marks'[FileYear])

Add [FileYear] from "year table" to the slicer,

Create measures

sum =
CALCULATE (
    SUM ( 'Ave Scaled Marks'[Scld] ),
    FILTER (
        ALLEXCEPT ( 'Ave Scaled Marks', 'Ave Scaled Marks'[FileYear] ),
        'Ave Scaled Marks'[StudentYearLevel]
            = MAX ( 'Ave Scaled Marks'[StudentYearLevel] )
            && 'Ave Scaled Marks'[StudentID] = MAX ( 'Ave Scaled Marks'[StudentID] )
    )
)

count =
CALCULATE (
    COUNT ( 'Ave Scaled Marks'[Scld] ),
    FILTER (
        ALLEXCEPT ( 'Ave Scaled Marks', 'Ave Scaled Marks'[FileYear] ),
        'Ave Scaled Marks'[StudentYearLevel]
            = MAX ( 'Ave Scaled Marks'[StudentYearLevel] )
            && 'Ave Scaled Marks'[StudentID] = MAX ( 'Ave Scaled Marks'[StudentID] )
    )
)

average = [sum]/[count]

Ly_sum =
CALCULATE (
    SUM ( 'Ave Scaled Marks'[Scld] ),
    FILTER (
        ALL ( 'Ave Scaled Marks' ),
        'Ave Scaled Marks'[FileYear]
            = MAX ( 'Ave Scaled Marks'[FileYear] ) - 1
            && 'Ave Scaled Marks'[StudentYearLevel]
                = MAX ( 'Ave Scaled Marks'[StudentYearLevel] ) - 1
            && 'Ave Scaled Marks'[StudentID] = MAX ( 'Ave Scaled Marks'[StudentID] )
    )
)

Ly_count =
CALCULATE (
    COUNT ( 'Ave Scaled Marks'[Scld] ),
    FILTER (
        ALL ( 'Ave Scaled Marks' ),
        'Ave Scaled Marks'[FileYear]
            = MAX ( 'Ave Scaled Marks'[FileYear] ) - 1
            && 'Ave Scaled Marks'[StudentYearLevel]
                = MAX ( 'Ave Scaled Marks'[StudentYearLevel] ) - 1
            && 'Ave Scaled Marks'[StudentID] = MAX ( 'Ave Scaled Marks'[StudentID] )
    )
)

average2 = [Ly_sum]/[Ly_count]

rank =
IF (
    NOT ( ISBLANK ( [average] ) ),
    RANKX (
        FILTER (
            ALL (
                'Ave Scaled Marks'[FileYear],
                'Ave Scaled Marks'[StudentYearLevel],
                'Ave Scaled Marks'[StudentID]
            ),
            'Ave Scaled Marks'[FileYear] = MAX ( 'Ave Scaled Marks'[FileYear] )
                && 'Ave Scaled Marks'[StudentYearLevel]
                    = MAX ( 'Ave Scaled Marks'[StudentYearLevel] )
        ),
        [average],
        ,
        DESC
    )
)

rank2 =
IF (
    NOT ( ISBLANK ( [average2] ) ),
    RANKX (
        FILTER (
            ALL (
                'Ave Scaled Marks'[FileYear],
                'Ave Scaled Marks'[StudentYearLevel],
                'Ave Scaled Marks'[StudentID]
            ),
            'Ave Scaled Marks'[FileYear] = MAX ( 'Ave Scaled Marks'[FileYear] )
                && 'Ave Scaled Marks'[StudentYearLevel]
                    = MAX ( 'Ave Scaled Marks'[StudentYearLevel] )
        ),
        [average2],
        ,
        DESC
    )
)

Please refer to my pbix for more details.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dphillips]

@v-juanli-msft Thanks for putting all this together. Obviously the separate table with the values in it means that you can look at previous years without it being influenced by the slicer. thanks for your help.