Top n Values

[tw15ns]

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 · Accepted solution]

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

[tw15ns]

WOW!

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

[Anonymous]

Very simple ideas. Use TOPN wrapped in DISTINCT.

[bengrove]

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.

[tw15ns]

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]

[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

[Anonymous]

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

[tw15ns]

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