topic Re: Top n Values in DAX Commands and Tips

Top n Values

Anonymous — Tue, 29 Jun 2021 10:24:54 GMT

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.

Re: Top n Values

Anonymous — Tue, 29 Jun 2021 10:52:31 GMT

Very simple ideas. Use TOPN wrapped in DISTINCT.

Re: Top n Values

Anonymous — Tue, 29 Jun 2021 11:50:03 GMT

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

Re: Top n Values

Anonymous — Tue, 29 Jun 2021 12:13:37 GMT

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

Re: Top n Values

Anonymous — Tue, 29 Jun 2021 12:25:05 GMT

[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

Re: Top n Values

Anonymous — Tue, 29 Jun 2021 12:54:57 GMT

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

Re: Top n Values

Anonymous — Tue, 29 Jun 2021 13:57:23 GMT

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

Re: Top n Values

Anonymous — Tue, 29 Jun 2021 14:04:06 GMT

WOW!

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

Re: Top n Values

bengrove — Tue, 31 Aug 2021 19:38:16 GMT

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.