cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rfrikha1992
Helper I
Helper I

Getting the max for every student for each Month

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 

 

Rfrikha1992_0-1631547377402.png

 

1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

Hi @Rfrikha1992 ,

 

Please follow these steps:

 

1. Add a Index column:

Eyelyn9_0-1631777559116.png

 

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:

Eyelyn9_1-1631777637650.png

 

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.

View solution in original post

5 REPLIES 5
Eyelyn9
Community Support
Community Support

Hi @Rfrikha1992 ,

 

Please follow these steps:

 

1. Add a Index column:

Eyelyn9_0-1631777559116.png

 

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:

Eyelyn9_1-1631777637650.png

 

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.

View solution in original post

Works perfectly ! Thanks 

ChrisMendoza
Super User
Super User

@Rfrikha1992 - 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

image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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.

 

@Rfrikha1992 - 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
        )
    )

image.png

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






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.