Top N filter for each category

[Enigma]

Hi, 

I have a simple table with two cols - Zone and Revenue. Zone values will always be either North, South, East or West.
I need to show the top 5 revenues for each of the four zones and filter out the rest of the rows. 

Something like this:

How do I achieve this?

[amitchandak · Accepted solution]

@Enigma , Create a new column

countx(filter(Table, [Zone] = earlier([zone]) && [revenue] <= earlier([revenue])),[revenue])

You can use this new column as filter <=5 in new table or measure

[Enigma]

Hi @amitchandak 

I tweaked your suggestion a little bit and used the below : 

COUNTROWS(filter(Table1, Table1[Zone] = EARLIER(Table1[Zone]) && Table1[Revenue] <= EARLIER(Table1[Revenue])))

... and then in the table visual, I used the TOP N filter.

Thanks much for helping out. Never knew about the Earlier function before!