Forum Discussion

Kiven_03's avatar
Kiven_03
Regular Visitor
2 years ago
Solved

Window Function for Student marks

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 😃

 

 


                                            



Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    Here is a radically different approach

    Add all marks together and then subtract the smallest mark.

     

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

    • Kiven_03's avatar
      Kiven_03
      Regular Visitor
      2 years ago

      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's avatar
        lbendlin
        Super User
        2 years ago

        let's say you have the following marks

         

        87,87,83,83,83,83

         

        What in your opinion should be the top 5 subjects?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Kiven_03
      Regular Visitor
      2 years ago

      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.