Forum Discussion
TOPN Function - Question/Comment
Okay we all know TOPN doesn't like ties and returns an error
My question is why then if you use TOPN as a filter in a CALCULATE does it include all tied values?
Very misleading - Consider this simple table?
Asset Net
| Asset 1 | 1000 |
| Asset 2 | 1000 |
| Asset 3 | 1000 |
| Asset 4 | 500 |
| Asset 5 | 250 |
And these Measure in order...
Total Net = SUM ( 'Table'[Net] )
Asset Rank = RANKX ( ALLSELECTED ('Table'[Asset]), [Total Net] )
Top Asset = TOPN ( 1, ALLSELECTED( 'Table'[Asset] ), [Asset Rank], ASC )
Top Asset Total Net = CALCULATE ( [Total Net], TOPN ( 1, ALLSELECTED ('Table'[Asset]), [Asset Rank], ASC) )If you only use the above Measures - Top Asset will give you an error while Top Asset Net will give you $3000
and you'll have no idea how many assets are tied for this $3000 total (10 at $300 or 2 at $1500 etc..)
So we have to use these Measures
Top Asset Name(s) = CONCATENATEX ( FILTER ( VALUES ( 'Table'[Asset] ), [Asset Rank] = 1 ), 'Table'[Asset], ", ", 'Table'[Asset], ASC ) Top Asset(s) Number = COUNTROWS ( FILTER ( VALUES ( 'Table'[Asset] ), [Asset Rank] = 1 ) )
And skip the Top Asset Measure to get a better overall picture! :smileyhappy:
Anyway just my 2 cents worth :smileyhappy:
Hopefully someone will find this helpful :smileyhappy:
Anonymous
Thanks for the suggestion (AVERAGEX) - the issue there is that my real table has multiple rows of data per asset per transaction
Hope this makes sense! :smileyhappy: So it doesn't give the correct result!
Anyway today I ran into another thing that could trip up the TOPN (seems very obvious now)
So the other thing that can trigger this message "Visual can't be displayed..." with the TOPN
When the Calendar rolls over into 2017 (a new year) however there's no data for 2017 (the new year) yet
You may think IF ( ISBLANK ( [TOPN Measure] ), "N/A", [TOPN Measure] ) would fix this but nope!
This patterns however seems to work with the RANKX measure
So for the TOPN Measure you'll have to use
Top Asset = IFERROR ( TOPN ( 1, ALLSELECTED ( Table[Asset] ), Table[Asset], ASC ), "N/A" )
Otherwise when your calendar rolls over into the new year but there's no data yet the visual won't display
BTW => this happens in a Table Visual where the first field is YEAR from a Calendar table (and the whole table won't display)
In a Card Visual the same would happen but only if you select 2017 from the YEAR slicer - then it would not display
Hope this makes sense! :smileyhappy:
6 Replies
Sean,
I agree that working with RANKX and TOPN (and other DAX!) can get convoluted. On your questions, as I understand it:
"Okay we all know TOPN doesn't like ties and returns an error"
TOPN doesn't care about ties. It will always return a table of rows that meet your Top N criteria. "Top Asset = TOPN(..." will always error as you are passing it a table and not a value?
"My question is why then if you use TOPN as a filter in a CALCULATE does it include all tied values?"
You are passing a table filtered down to the Top N rows back to the CALCULATE, which might include several rows if there is a tie in the rankings or your TOPN is greater than 1.
Anonymousyou are 100% correct on both points! I used the wrong word => "Error" => Clearly not really an error!
However perhaps this picture will show what I mean and what happens when you do have ties
So you get a missing Visual for Top Asset but right next to it Top Asset Net displays the total amount generated by the Top asset?
I meant to say that this leads to ambiguity and uncertainty which can be cleared with the other 2 Measures
one lists the tied assets and the other shows the count of the tied assets
BTW I would've left the report the original way but I saw this happening by pure chance in one month in 2012 in my data
I was just testing out slicers - otherwise if you don't filter the data there are no ties overall
Finally while writing this I'm thinking to even modifiy the Top Asset Net so it actually divides the result by Top Asset(s) Number
basically the 3000 / 3 = 1000 so you see the amount generated by each Top Asset and not the sum of all Top Assets
Hope this makes sense! :smileyhappy:
Sean,
You seem to dig into the murkier areas of DAX - this is another arcane mystery a bit like the affect of Sort By Column on ALL filters...
Sorry, I missed your point that your original 'Top Asset' measure actually returns a valid value if you don't have ranking ties - you were absolutely right but I didn't see how that should happen. Re-reading the doco, I see now that TOPN return a table of rows and, if that table has one row (i.e. no ties) AND one column, then the measure will not error out.
Thinking it through aloud, the following variation errors out as now expected, with "The expression refer to multiple columns..." - one row, but more than one column. Though more inefficient with the unneeded columns, it will work if wrapped in a CALCULATE, SUMX, CONCATENATEX etc. that can handle the extra columns...
Top Asset = TOPN ( 1, ALLSELECTED( 'Table' ), [Asset Rank], ASC )
I like your CONCATENATEX measure to list out joint top-ranked items, and agree that averaging the Top Asset Value is more accurate. I came up with this version, though using just 'Table'[Net] in the ALLSELECTED would presumably be more efficient:
Top Asset Value =
AVERAGEX (
TOPN ( 1, ALLSELECTED ( 'Table' ), 'Table'[Net], DESC ),
'Table'[Net]
)Thanks for stretching my brain. For me the lesson is to think of TOPN as TOPNROWS, and always wrap it in an aggregator for safety.
It's not an error, but it IS obscure I think - I wish these wrinkles were documented as Remarks on the related MSDN page or somewhere else central...
Dear Sean
Can u please check this link , it give u some idea to achieve your goal.
https://blogs.msdn.microsoft.com/danrub/2016/03/19/dynamic-topn-ranking-in-power-bi/
