Above Top N

[danextian]

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. 

[Sean · Accepted solution]

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!

[danextian]

Hi @Sean,

Thanks! Your second alternative works for me. I wonder though why the first one doesn't.

[Anonymous]

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 🙂

[OwenAuger]

Just offering a variation on the excellent solution already presented.

The EXCEPT function is also useful for taking the complement of a set of values:

All Less Top 5 with EXCEPT =
CALCULATE (
    [Total Measure],
    EXCEPT (
        ALL ( Table[Column] ),
        TOPN ( 5, ALL ( Table[Column] ), [Rank Measure] )
    )
)

[Sean]

@OwenAugerI have in fact seen you use EXCEPT in other posts (so I should have thought of this also)
The only thing I noticed - the TOP 5 should be based on the Total Measure not the Rank Measure

All Less Top 5 with EXCEPT =
CALCULATE (
    [Total Measure],
    EXCEPT (
        ALL ( Table[Column] ),
        TOPN ( 5, ALL ( Table[Column] ), [Total Measure] )
    )
)

Great job as always!

[OwenAuger]

@Sean

Oops yes - I had mis-interpreted what those measures represented!

I assumed Rank Measure was used for ranking and then the calculation itself used the Total Measure.

Good job yourself - cheers 🙂

[Anonymous]

That is so boss.

[Anonymous]

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).

[danextian]

@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.