Filter top x per id

user16640

Hi All,

 

I will just draw this as it will be easier to understand. I have alist of account with revenue atatched per country bu I only want to show the top x countries in revenue (top 2 in the example below). My tab is already sorted by Account and Revenue:

 

Here is what it looks like now:

Account	Country	Revenue
Account1	US	$100,00
Account1	AU	$50,00
Account1	UK	$20,00
Account1	CA	$10,00
Account2	US	$100,00
Account2	JP	$50,00
Account2	KR	$20,00
Account2	CA	$10,00
Account3	SG	$100,00
Account3	US	$50,00
Account3	CA	$20,00
Account3	TH	$10,00

 

I would like to have top 2 country only: 

Account	Country	Revenue
Account1	US	$100,00
Account1	AU	$50,00
Account2	US	$100,00
Account2	JP	$50,00
Account3	SG	$100,00
Account3	US	$50,00

 

Finally I think the answer is no unless I use ETL but will ask any way, would there be any way to get: 

I want to see the top x countries listed in one cell and get the SUM of revenue in the other one (SUM of revenue could be all countries or just those top x, either way this would work.)

Account	Country	Revenue
Account1	US, AU	SUM all revenue
Account2	US, JP	SUM all revenue
Account3	SG, US	SUM All revenue

 

Thanks for your help!

 

Cartergan

You could do BeastMode calculations right on the card you are building. One idea coming to mind would be doing some sort of CASE statement to make a new value:

CASE WHEN Country IN (whatever specifications you'd like) THEN 1 

WHEN Country IN (second specifiations) THEN 2

etc...

This way would allow you to group certain countries and then you can sum the revenue column once their grouped. 

 

user16640

Thanks Cartergan.

The issue with your solution is that I do not want to select a set list of countries but I want to select the top ones based on revenue. 

I do not know how/if I can get a top vlaues retunred with beastmode..

 

Anyone else?