Filter top x per id

user16640
user16640
in Charting

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:

AccountCountryRevenue
Account1US$100,00
Account1AU$50,00
Account1UK$20,00
Account1CA$10,00
Account2US$100,00
Account2JP$50,00
Account2KR$20,00
Account2CA$10,00
Account3SG$100,00
Account3US$50,00
Account3CA$20,00
Account3TH$10,00

 

I would like to have top 2 country only: 

AccountCountryRevenue
Account1US$100,00
Account1AU$50,00
Account2US$100,00
Account2JP$50,00
Account3SG$100,00
Account3US$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.)

AccountCountryRevenue
Account1US, AUSUM all revenue
Account2US, JPSUM all revenue
Account3SG, USSUM All revenue

 

Thanks for your help!

 

Comments

  • Cartergan
    Cartergan Contributor

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

This discussion has been closed.

Categories