Forum Discussion
Above Top N
Hi All,
I'm trying to create a measure that will show the amount of those not included in top 5.
I got this formula from the web
Top5 = calculate([calc measure],TOPN(6,'table',[calc measure]))
If this formula showed the correct value, then I would have used [calc measure]-top5 to show top 6 and up.
Comparing the result with a visual that shows just the top 5 and manually summing up their values, the top5 measure always has a higher value.
1) Specify the column (top 5 what?) in the TOPN
Top 5 Measure = CALCULATE ( [Total Measure], TOPN ( 5, ALL ( Table[Column] ), [Total Measure] ) )
You can then subtract the above Measure from a Total Measure (one that produces overall total)
All Less Top 5 = [Total Measure] - [Top 5 Measure]
2) Alternatively you can get the same result using RANKX instead (again specify what you are ranking)
Rank Measure = RANKX ( ALL ( Table[Column] ), [Total Measure] )
Then the new Measure would look like this
All Less Top 5 = CALCULATE ( [Total Measure], FILTER ( ALL ( Table[Column] ), [Rank Measure] > 5 ) )
Hope this helps! :smileyhappy:
9 Replies
1) Specify the column (top 5 what?) in the TOPN
Top 5 Measure = CALCULATE ( [Total Measure], TOPN ( 5, ALL ( Table[Column] ), [Total Measure] ) )
You can then subtract the above Measure from a Total Measure (one that produces overall total)
All Less Top 5 = [Total Measure] - [Top 5 Measure]
2) Alternatively you can get the same result using RANKX instead (again specify what you are ranking)
Rank Measure = RANKX ( ALL ( Table[Column] ), [Total Measure] )
Then the new Measure would look like this
All Less Top 5 = CALCULATE ( [Total Measure], FILTER ( ALL ( Table[Column] ), [Rank Measure] > 5 ) )
Hope this helps! :smileyhappy:
Ya, Sean noticed the important missing ALL( ) ! As soon as you split your measure out ... it's evaluating each column 1 at a time. So, the data only has the 1 column of data, and you are saying "Am I in the top 5, of this... set of 1?" and of course you are.
With the ALL( ) you are now saying "am I in the top 5 in the set of ALL values?" which makes entirely more sense :)
Top5 = calculate([calc measure],TOPN(6,'table',[calc measure]))
Not sure if the "6" was on purpose? I would expect a 5... for the Top 5 :)
Other than that... the measure looks correct to me, and will evalate [calc measure], when table is filtered to just include the top 5/6, ordered by [calc measure]. (So, if [calc measure] is SUM('table'[value]), Top5 will return the total sum of values 1-5).
Anonymous my bad, typo error.
Anyway, the first image below is of a visual with top N visual filter. The second one, is DAX TopN. As you can see, the second one has an extra column. The weird thing is that no matter how many times i change the DAX N value for the second visual, it remains the same.
