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 |
Thanks for your help!
Comments
-
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.
0 -
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?
0
