Window Function for Student marks

[Kiven_03]

Hi,
I have issue in finding a solution, please help me with it.

I have a table "X_Marks",
where columns are (ResultYear (ex: 2021-2022), School_ID, Roll_Number, Subject_Code, Marks).
The ResultYear has 2 values (2021-2022 & 2022-2023), and Roll_Number are not unique it has one duplicate value but unique in each ResultYear.

I need a window function of find top 5 subject marks out of 6 subjects of each student(Roll_Number). I tried many things but failed to achieve the solution.
My DAX :
Top5SubjectTotal = CALCULATE([Total Marks], FILTER( ALLSELECTED(Roll_Number), TOPN(5, Marks, DESC));

Issue :

It worked but, if any subject marks are equal
(ex: Subject1 = 99, Subject2 = 99, Subject3 = 83, Subject4 = 83, Subject5 = 87, Subject6 = 92) 

here 6 subject marks comes as Top3 (as 2 subject marks are equal), So total come as 543 instead of 460.

Thank you for your time,
Vivek 😃

[Anonymous]

Hi @Kiven_03 

Thanks for the reply from @lbendlin .

Maybe you can try the following:

Top5 = 
SUMX(
    TOPN(
        5, 
        'Table',
        'Table'[Marks]
    ), 
    'Table'[Marks]
)

Output:

Best Regards,
Yulia Xu

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

[Kiven_03]

Thank you very much @Anonymous .

I have already tried this and It didn't work.
If 50,50,66,72,93,73 are the subjects marks then total marks of 6 subject is 404, and total marks of top 5 is also 404(because all 6 subjects comes under top 5 as 2 subject values are equal). If any marks are equal I am not able get the top 5 total marks.

I really appericiate for your time 😃, 
Thank you very much.
Mean while if I get the solution I will post it.

[lbendlin]

what is your expected result in case of ties? Is it ok to randomly pick subjects?

[Kiven_03]

Hi @lbendlin ,
Thank you for your reply,
I was trying to get the total of top5 subject marks out of 6 subjects of a student,

but thing is in student's Marks Table of 3 academic years  where student roll in not unique, but unique in each academic year.

Thank you. 

[lbendlin]

let's say you have the following marks

87,87,83,83,83,83

What in your opinion should be the top 5 subjects?

[Kiven_03]

@lbendlin I was trying to eliminate the 6th value using window function INDEX or MIN function,

but struggling to create a DAX for the expression for it, maybe it bring me the result.

Please help me with it and I appreciate for your time.

[lbendlin · Accepted solution]

Here is a radically different approach

Add all marks together and then subtract the smallest mark.

[Kiven_03]

Thanks for your reply @lbendlin ,

87,87,83,83,83 should be my total of Top5 Subjects and one subject marks needs to be eliminated '83'. 

But now I got new doubt, thank you for your valuable reply.

Anyhave please help me with the current senario.

Thank you

Vivek