5 Replies Latest reply on Sep 16, 2013 6:11 AM by Jonathan Drummey

# SUM the top x values

I am creating a dashboard to benchmark individual assets against the group and I want to give the users the option to select top/bottom performers to compare against. I have figured out how to display individual charts for each of the top X by using the index formula but what I really want is to aggregate the data and get 1 chart. Any ideas?

I have dabbled a bit with custom SQL where I use a parameter in combination with LIMIT in the SQL szntax. This works well for 1 chart but for multiple I would need to create a new connection for each chart which I want to avoid. I would prefer a solution done inside Tableau.

• ###### 1. Re: SUM the top x values

Markus,

You might take a look at this knowledge base article for how to use Sets (instead of Index()) to accomplish top/bottom: Creating Sets for Top N and Others | Tableau Software.  The benefit of this approach is that it is much simpler and keeps the top/bottom as a row level calculation so it will be much easier to use additional calculations (row, aggregate, or table) -- it should be fairly easy to get the sum.  Also, you should be able to get both Top and Bottom in a single chart (using a combined set of Top and Bottom).

If you have any issues, please post a packaged workbook and I'd be happy to take a look!

Regards,

Joshua

• ###### 2. Re: SUM the top x values

Thanks for the article Joshua!

It helped a bit and I can now find the top x performers based on a field but I still dont know how to make calculations based on the set. What I want is a chart with one column for a single customer of my choosing and then the sum/avg/whatever of  the top X customers next to it. I do not want to have a column for each member of the set, I want an aggregated column for the set. Is that possible?

• ###### 3. Re: SUM the top x values

Could the selected customer of your choosing be a member of the top X customers or not? The approach might need to change based on that.

For an idea of how to use sets in calculations, see http://www.tableausoftware.com/about/blog/2013/4/lets-talk-about-sets-23043.

• ###### 4. Re: SUM the top x values

Hi Jonathan,

I have a similar problem in getting the percentage of any selected customers (not top n nor bottom n) compared to the total customers. Grouping seems works but not dynamic in selecting customers, any suggestion?

Many thanks

Jason

• ###### 5. Re: SUM the top x values

Hi Jason,

Are you looking to select a single customer (or have a row for each customer), or to bundle the selected customers into a group comparison vs. total?

Jonathan