topic Re: TOPN sorting in DAX Commands and Tips

TOPN sorting

traviscole — Thu, 05 Aug 2021 20:34:41 GMT

I am hardcore failing at making TOPN work in DAX instead of the filters on a visual. I have tried and looked at a million different things and ways of doing it in my real data set and this dummy sample I made, and failed at every step of the way, so am giving up for now and seeing if someone can show me what the correct way to do it is.

All I need is the proper DAX to make a measure that will return the TOPN for by Volume for a dummy data set like below.

My latest failure is:

Measure = TOPN(5, Volume, Volume[Volume], desc)

And it gives me 'expression refers to multiple columns, etc.'

I've gotten this error and others one after another, and am handing it over to someone with more knowledge than me to knock it out in 5 minutes. Thanks in advance.

Re: TOPN sorting

AlexisOlson — Thu, 05 Aug 2021 21:09:39 GMT

If you check the documentation for TOPN (here and/or here), you may notice that it returns a table as its output, not a single value. A measure can only return a single value.

The easiest approach is usually to use the Top N filtering option in the filter pane.

See here too: https://community.powerbi.com/t5/Desktop/TopN/m-p/131646

Re: TOPN sorting

Jihwan_Kim — Fri, 06 Aug 2021 10:55:44 GMT

Volume total : =

SUM(Volume[Volume])

Top five volume measure : =

CALCULATE (

[Volume total :],

KEEPFILTERS ( TOPN ( 5, ALL ( Items ), [Volume total :], DESC ) )

)

Link to the sample pbix file

Re: TOPN sorting

traviscole — Fri, 06 Aug 2021 13:01:31 GMT

This appears to be the explanation I was looking for. I feel really dumb after seeing it, I blame being tired at the end of a day and week. Will post back in a little after I run it through the real data set.

Update: It works! Only problem having now in the real dataset is when I try to use a measure I have built that spits out what I want the TOPN to be (instead of using an actual number) it's funky and goes from sorting the top 95 when I use 'TOPN(95' to sorting the top 25 when I use 'TOPN([measure]' (but still keeps the total at the bottom of the table as the total of TOP95, but only showing TOP25 items, lol. Gonna do some reading and look into what is up, but I've triple checked the measure and it's spitting out a 95 everywhere else I use it.

Re: TOPN sorting

traviscole — Fri, 06 Aug 2021 15:11:43 GMT

Yeah, that's the easy way for us people in the office to do it. The goal is to make it go automatically by another measure that I have built to spit out a number for what I want the TOPN to be so on the user side they don't have to do anything and break the report or do god knows what. I wish in that TOPN i nthe filter pane I could just drag in the measure instead of having to manually input the number, but it is what it is.