Forum Discussion

traviscole's avatar
traviscole
Advocate I
5 years ago
Solved

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.

 

 

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Volume total : =
    SUM(Volume[Volume])

     

     

    Top five volume measure : =
    CALCULATE (
    [Volume total :],
    KEEPFILTERS ( TOPN ( 5, ALL ( Items ), [Volume total :], DESC ) )
    )
     
     
     
    • traviscole's avatar
      traviscole
      Advocate I
      5 years ago

      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.

    • traviscole's avatar
      traviscole
      Advocate I
      5 years ago

      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.