Filter top x per id

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

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

• 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?

This discussion has been closed.