cancel
Showing results for
Did you mean:  Helper I

## Sum first 3 ranks

Hi, I have a table with students results and I want to sum the top 3 grades. However, a student can have the same subject multiple times with the same result. I am putting the grades into a ranking order and then summing the top 3, however if a grade is duplicated and has as rank of 1,2 or 3 that grade is inlcuded in the sum. In the below example I am expecting grade_sum to be 12, adding 6+4+2, but its adding 6+4+2+2 as there are two rows with the subject as lang with a grade of 2, meaing it has a rank of 3.

 student id subject grade Rank grade_sum test1 1 maths 6 1 14 test1 1 geo 4 2 14 test1 1 lang 2 3 14 test1 1 lang 2 3 14 test1 1 lit 1 4 14 test1 1 lit 1 4 14

Rank =
RANKX (
CALCULATE (
ALLEXCEPT (
'Table',
'Table'[id],
)
)
)

CALCULATE (
ALLEXCEPT ( 'Table', 'Table'[id])
)
6 REPLIES 6  Community Support

Hello @ckausihan_12

You can test my dax to create calculated columns.

``Rank = RANKX (FILTER('Table','Table'[id]=EARLIER('Table'[id])),'Table'[grade],,DESC,Dense)``
``````Sum of Top 3 Per Student =
SUMX (
FILTER (
SUMMARIZE (
'Table',
'Table'[student],
'Table'[id],
'Table'[subject],
'Table'[Rank]
),
[id] = EARLIER ( [id] )
&& [Rank] <= 3
),
)``````

The result is as follows: Best regards

Rico Zhou

If this post helps,then consider Accepting it as the solution to help other members find it faster.  Helper I

How will this work if there are muktipes of rank 1 or 2? At the moment those duplicates are included in the 'Sum of Top 3 Per Student' calculation.  Super User

@ckausihan_12 , Try like

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!  Helper I

@amitchandak This didn't work. Frequent Visitor

It seems that Lang has 2 identical rows which is not incorrect as you mention but is causing the code to return both hence the incorrect result. I Would add an index column in the table (you can easily add from Query editor) and then create a new column that concatenates ID or Name with the index to create a unique row, and then adjust your code (ALLEXCEPT) to use the new column. Hope this helps.  Helper I

I tried what you suggested. But using the new unique column in the ALLEXCEPT isn't adding the grades together. The table contains multiple students.  