Forum Discussion

ckausihan_12's avatar
ckausihan_12
Helper I
5 years ago

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])
)
Need Help
Show and Tell
Tutorial Requests

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      ckausihan_12
      Helper I
      5 years ago

      Anonymous 

       

      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.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    ckausihan_12 , Try like

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

    • ckausihan_12's avatar
      ckausihan_12
      Helper I
      5 years ago

      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.