Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
traviscole
Advocate I
Advocate I

TOPN sorting

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.

 

topn.jpg

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Picture1.png

Volume total : =
SUM(Volume[Volume])

 

 

Top five volume measure : =
CALCULATE (
[Volume total :],
KEEPFILTERS ( TOPN ( 5, ALL ( Items ), [Volume total :], DESC ) )
)
 
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Picture1.png

Volume total : =
SUM(Volume[Volume])

 

 

Top five volume measure : =
CALCULATE (
[Volume total :],
KEEPFILTERS ( TOPN ( 5, ALL ( Items ), [Volume total :], DESC ) )
)
 
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.

AlexisOlson
Super User
Super User

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.

AlexisOlson_0-1628197651953.png

 

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

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors