Getting the max for every student for each Month

[Anonymous]

Greeting lovely memebers , 

let's say i have a table where it contains the list of the student , their mark for each subject and the month number .

im trying to create a calculated column ( exemple : Max_value column in the picture below)

this column will contain the max mark for every student in every month .

i was able to get the max value for each student but i cant get the max for each month aswell .

Below is my output , as you can see Max_value column is what im trying to acheive. Thanks all in advance 

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

Please follow these steps:

1. Add a Index column:

2. Use RANKX() to rank by Mark based on each Month Number and Student ID:

Rank by Mark =
RANKX (
    FILTER (
        'Table',
        [Month Number] = EARLIER ( 'Table'[Month Number] )
            && [Student ID] = EARLIER ( 'Table'[Student ID] )
    ),
    [Mark],
    ,
    DESC,
    DENSE
)

3. Use RANKX() again to rank based on the Rank column and Index column:

Max_Value =
VAR _rank2 =
    RANKX (
        FILTER (
            'Table',
            [Month Number] = EARLIER ( 'Table'[Month Number] )
                && [Student ID] = EARLIER ( 'Table'[Student ID] )
        ),
        [Rank by Mark] * 10 + [Index],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( _rank2 = 1, [Mark], BLANK () )

Here is the final output:

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

[Anonymous]

Works perfectly ! Thanks 

[ChrisMendoza]

@Anonymous - Not exactly the output you are looking for but the calculations are correct.

Max_Value = 
VAR student = TableName[Student ID]
VAR monthNum = TableName[Month Number]
VAR Result =
CALCULATE(
    MAX(TableName[Mark]),
    FILTER(
        TableName,
        TableName[Student ID] = student
    ),
    KEEPFILTERS(TableName[Month Number] = monthNum)
)
Return 
    Result

Month Max_Value = 
VAR monthNum = TableName[Month Number]
VAR Result =
CALCULATE(
    MAX(TableName[Mark]),
    FILTER(
        TableName,
        TableName[Month Number] = monthNum
    )
)
Return 
    Result

[Anonymous]

unfortuanately not the output that im looking for .

what i need is for each month the hight rank for each student as it shows in the picture above.

[ChrisMendoza]

@Anonymous - Seems I can't get your expected result. The closest I could get is below:

Max_Value = 
VAR student = TableName[Student ID]
VAR monthNum = TableName[Month Number]
VAR Result =
CALCULATE(
    MAX(TableName[Mark]),
    FILTER(
        TableName,
        TableName[Student ID] = student
    ),
    KEEPFILTERS(TableName[Month Number] = monthNum)
)
Return 
    CALCULATE(
        MAX(TableName[Mark]),
        FILTER(
            ALLEXCEPT(TableName,TableName[Mark]),
            TableName[Mark] = Result
        )
    )

Hopefully someone else can show us both how to get your desired result.