topic Re: Window Function for Student marks in DAX Commands and Tips

Window Function for Student marks

Kiven_03 — Thu, 27 Jun 2024 12:08:32 GMT

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 😃

Re: Window Function for Student marks

lbendlin — Fri, 28 Jun 2024 01:06:54 GMT

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

Re: Window Function for Student marks

Anonymous — Fri, 28 Jun 2024 07:08:42 GMT

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.

Re: Window Function for Student marks

Kiven_03 — Fri, 28 Jun 2024 16:42:31 GMT

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.

Re: Window Function for Student marks

lbendlin — Fri, 28 Jun 2024 16:27:50 GMT

let's say you have the following marks

87,87,83,83,83,83

What in your opinion should be the top 5 subjects?

Re: Window Function for Student marks

Kiven_03 — Fri, 28 Jun 2024 16:41:09 GMT

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.

Re: Window Function for Student marks

Kiven_03 — Sat, 29 Jun 2024 20:32:17 GMT

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

Re: Window Function for Student marks

Kiven_03 — Sat, 29 Jun 2024 21:23:24 GMT

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

Re: Window Function for Student marks

lbendlin — Sun, 30 Jun 2024 01:19:39 GMT

Here is a radically different approach

Add all marks together and then subtract the smallest mark.