Getting the max of a column group by another column

[Anonymous]

Greeting lovely member . 

i have the following Table that contain the student ID and their mark : 

im trying to add a calculated Column that have "Max" on the max mark for each student but honestly im struggling :  . 

here the output that im looking for : 

Thanks all in advance 🙂 

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

Try the following steps:

step1,use power query editor ,add a new  index column(you also could copy table then new table to to add  index):

Then use the following dax to create new column:

Max Mark = 
IF(
    RANKX (
        FILTER ( 'Table', 'Table'[Student ID] = EARLIER ( 'Table'[Student ID] ) ),
        'Table'[Mark],
        ,
        DESC,
        DENSE
    )=1,1,-999)+
    RANKX (
        FILTER ( 'Table', 'Table'[Student ID] = EARLIER ( 'Table'[Student ID] ) ),
        'Table'[Index],
        ,
        DESC,
        DENSE
    )

MAX Mark2 = IF(RANKX (
        FILTER ( 'Table', 'Table'[Student ID] = EARLIER ( 'Table'[Student ID] ) ),
       'Table'[Max Mark],
        ,
        DESC,
        DENSE
    )=1,"Max",BLANK())

Wish it is helpful for you!

Best Regards

Lucien

[Anonymous]

Thnaks all for your answers , all those answers works , but if there is 2 marks that are both have the Max values i will have "Max" for both of them . 

is there a way i can have Max only for 1 of them ? 
Thanks in advance 🙂 

[Fowmy]

@Anonymous 

You need to add an index column and modify create the calculated column as follows:

Max Marks = 

VAR __id = Table3[Studen ID]
var __max = MAXX(FILTER(Table3 , Table3[Studen ID] = __id ), Table3[Marks] + Table3[Index])
return
IF( (Table3[Marks] + Table3[Index]) = __max , "Max")

[Jihwan_Kim]

https://www.dropbox.com/s/56yv2ibnjxklo4i/rfrikha.pbix?dl=0 

Max mark CC =

IF (

MAXX (

FILTER ( 'Table', 'Table'[StudentID] = EARLIER ( 'Table'[StudentID] ) ),

'Table'[Mark]

) = 'Table'[Mark],

"Max"

)

[Fowmy]

@Anonymous 

Max Marks = 
IF(
    Table3[Marks] = 
    CALCULATE(
        MAX(Table3[Marks]),
        ALLEXCEPT(Table3 , Table3[Studen ID] )
    ),
    "Max"
)

[amitchandak]

@Anonymous , create a new column like

new column =
var _1 = maxx(filter(Table, [Student ID] = earlier([Student ID]) ),[MArks])
return
if( [MArks] =_1, "Max", blank())