Sum first 3 ranks

[ckausihan_12]

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 (

ALL ('Table'[grade]),

CALCULATE (

MAX('Table'[grade]),

ALLEXCEPT (

'Table',

'Table'[id],

'Table'[grade]

)

)

)

grade_sum =

CALCULATE (

SUMX(TOPN ( 3, 'Table', FIRSTNONBLANK('Table'[rank],'Table'[grade]), ASC ), 'Table'[grade] ),

ALLEXCEPT ( 'Table', 'Table'[id])

)

[v-rzhou-msft]

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'[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

Best regards

Rico Zhou

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

[ckausihan_12]

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.

[amitchandak]

@ckausihan_12 , Try like

Top 3 = CALCULATE(sum('Table'[grade_sum]),TOPN(3,ALL ('Table'[grade]),calculate(MAX('Table'[grade])),DESC),VALUES('Table'[grade]))

[ckausihan_12]

@amitchandak This didn't work.

[QQCFA]

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.

[ckausihan_12]

@QQCFA 

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.