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

Very simple ideas. Use TOPN wrapped in DISTINCT.

  • 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

       

      [TOPN Sum] =
var topn_ = 3
var Result =
    SUMX(
        DISTINCT( Result[ID] ),
        CALCULATE(
            SUMX(
                TOPN(topn_,
                    DISTINCT( Result[Result] ),
                    Result[Result],
                    DESC
                ),
                Result[Result]
            )
        )
    )
return
    Result

       

      Works correctly also on the Total row...

       

      Another way of calculation:

      [TOPN Sum] =
var topn_ = 3
var IDsWithTopNResults = 
    GENERATE(
        DISTINCT( Result[ID] ),
        CALCULATETABLE(
            TOPN(topn_,
                DISTINCT( Result[Result] ),
                Result[Result],
                DESC
            )
        )
    )
var Result =
    SUMX(
        IDsWithTopNResults,
        Result[Result]
    )
return
    Result
  • bengrove's avatar
    bengrove
    New Member
    5 years ago

    How about trying to get a top ten by client, and by master client? Something like the below.

     

    When I try to do this in BI using TopN on client name by sales amount, it only filters client name, i.e. if sub client 1 has 300k, and sub client 2 has 200k, it skips to the next master client that has that 200k.