Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ckausihan_12
Helper I
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.

 

studentidsubjectgradeRankgrade_sum
test11maths6114
test11geo4214
test11lang2314
test11lang2314
test11lit1414
test11lit1414

 

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])
)
6 REPLIES 6
v-rzhou-msft
Community Support
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'[grade],
            'Table'[Rank]
        ),
        [id] = EARLIER ( [id] )
            && [Rank] <= 3
    ),
    [grade]
)

The result is as follows:

1.png

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.

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
Super User
Super User

@ckausihan_12 , Try like

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

@amitchandak This didn't work.

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.