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

 

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