Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Top n Values

I am tryng to use Power BI in a school setting and want to be able to get the sum of the top 3 results for each student. I have tried TOPN but the problem is that if a student's top results are say 4...
  • Anonymous's avatar
    Anonymous
    5 years ago

     

    [TOPN Sum] =
var topn_ = 3
return
SUMX(
    DISTINCT( Result[ID] ),
    CALCULATE(    
        var VisibleSubjects =
            DISTINCT( Result[Subject] )
        var SubjectsOrdered =
            ADDCOLUMNS(
                VisibleSubjects,
                "@SubjectNumber",
                    RANKX(
                        VisibleSubjects,
                        Result[Subject],,
                        ASC,
                        // this option
                        // does not matter
                        // here
                        DENSE 
                    )
            )
        var SubjectNumberMagnitude =
            LEN(
                MAXX(
                    SubjectsOrdered,
                    [@SubjectNumber]
                )
            )
        var ResultWithSubject =
            SUMMARIZE(
                Result,
                Result[Result],
                Result[Subject]
            )
        var Result = 
            SUMX(
                TOPN(topn_,
                    NATURALINNERJOIN(
                        ResultWithSubject,
                        SubjectsOrdered
                    ),
                    Result[Result]
                        * 10^SubjectNumberMagnitude
                        + [@SubjectNumber]
                ),
                Result[Result]
            )
        return
            Result
    )
)

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thanks for the reply. I understand the logic of your solution unfortunately I cannot get it to work.

I write the measure as below but it still seems to be totalling up all instances of the top values e.g. the third student has top results of two at 8.5 and 3 at 8 hence a total of 41. Am I doing something stupid!!

Thanks again

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Then do it the other way round: first DISTINCT, then TOPN. That should definitely get you what you need.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks and sorry for being a pain but your solution works too well. With the list below it gives a result of 21 (sum of the top 3 distinct values), but what I need is to add together the two 8s and  then only one of the 7s. I can do this in Excel with the LARGE function but not in DAX.

    • Art - 8
    • Maths - 8
    • Science - 7
    • Spanish - 7
    • English - 6
    • Music - 5

    Thanks again