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 grade 8s then TOPN would return 32 rather than the 24 I want. Any ideas as how to resolve this would be appreciated.  

  • 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
    )
)

     

8 Replies

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

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

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

      WOW!

       

      I have no idea how this works but it does the job. Thanks for all your help.