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.

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.