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.
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'[grade], 'Table'[Rank] ), [id] = EARLIER ( [id] ) && [Rank] <= 3 ), [grade] )
The result is as follows:
You can download the pbix file from this link: Add the first 3 ranges
If this post helps,then consider Accepting it as the solution to help other members find it faster.
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.
@ckausihan_12 , Try like
Top 3 = CALCULATE(sum('Table'[grade_sum]),TOPN(3,ALL ('Table'[grade]),calculate(MAX('Table'[grade])),DESC),VALUES('Table'[grade]))
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.
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.
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.