11 Replies Latest reply on Jul 19, 2018 6:59 AM by dinesh punnam

# How to filter by top (10%, 20%, etc.) percent based on underlying data

I would like to create a filter (perhaps a slider) which allows me to select the top 10%, 20%, etc. percent of donors within each relevant region.  I've tried ranking but I don't want to include the individual ID_number level in my view.  (see 1st tab in the attached workbook).

• ###### 1. Re: How to filter by top (10%, 20%, etc.) percent based on underlying data

Hi Heather, took a look at your workbook. You didn't mention what you want to base your top 10%, 20%. on? AG_COMM_AMT? Campaign Total? or CAP_COMM_AMT? Or something else?

--Shawn

• ###### 2. Re: How to filter by top (10%, 20%, etc.) percent based on underlying data

Heather, I am not entirely sure that the technique in the attached guide achieves what you want, but your question prompted an interesting challenge, so I put together a mini-tutorial using sample data.

Thank you to Joe Mako for his help with this.

• ###### 3. Re: How to filter by top (10%, 20%, etc.) percent based on underlying data

It would be great if one could see contribution of each of the top N customers using the colour shelf....and better still what percentage of total sales for each region the Top N% account for. Have racked my brain for a few minutes and cannot figure how one would add that level of detail.

• ###### 4. Re: How to filter by top (10%, 20%, etc.) percent based on underlying data

Awesome tutorial, thanks, Dimitri!

• ###### 5. Re: How to filter by top (10%, 20%, etc.) percent based on underlying data

The solution provided by Dimitri got me to the metrics I was looking for but now my performance has slowed to a crawl.  I played around with attempting to optimize the table calc (per some solutions offered by Richard Leeke ). http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html#more

It seems odd to me that two calculated fields are affecting the entire performance of the workbook...

• ###### 6. Re: How to filter by top (10%, 20%, etc.) percent based on underlying data

Table calculations are known to do that sometimes.

You must have a lot of transactional data in your extract - did you try re-extracting it and aggregating? May not be possible in your case, because of calculated fields, but anything that reduces data size should help.

As Richard Leeke said, offloading as much processing as possible to the database helps as well. This is the approach I take - aggregate as much as possible in Oracle, then let Tableau built an extract.

• ###### 7. Re: How to filter by top (10%, 20%, etc.) percent based on underlying data

Thanks - yes, I did try re-extracting but I do have a lot of transactional data.  It doesn't seem to be optimizing my calculations...is there a way I can confirm that?  Or understand why it's not?

• ###### 8. Re: How to filter by top (10%, 20%, etc.) percent based on underlying data

Optimizing extract might make a difference only if table calculations were created/modified after the extract creation. If extract is done with calculation already in place, there would be nothing to optimize.

But even with optimized extract, there is a physical limit on performance, especially with lots of data and table calculations.

One way to tackle this would be to duplicate the data source just for the sheet that is slow, and aggregate it as much as you can. That way your workbook will have two datasources containing the same data - transactional records for other analysis and aggregated for the sheet that uses table calculations.

Of course this can produce be side effects, i.e. size of the workbook, global filters need to be duplicated, etc.

• ###### 9. Re: How to filter by top (10%, 20%, etc.) percent based on underlying data

Thanks  - sounds like an interesting workaround.  I'll give it a shot.

• ###### 10. Re: How to filter by top (10%, 20%, etc.) percent based on underlying data

Hi Dimitri,

Thanks for this great tutorial! One thing which I still will add more to this would be a way to display the range of values inside each percentage slot like what is range of Sales within the Top 20% and display them along side the color legend.

I tried but in vain. Can suggest something for this?

Thanks!

• ###### 11. Re: How to filter by top (10%, 20%, etc.) percent based on underlying data

Hello All,

I am trying to achieve below

Top 5 from the worksheet Value

Hope you guys are doing great! I was wondering if we have a feature in tableau where we can pull top 5 clients (in my case) close to the  +15%  --> -15%  Revenue on the worksheet,

In above scree shot I have selected a client and calculated total Gross revenue and is there any way that i can get 5 more  clients which are having total Gross revenue +15% t o -15% close this 21.53M number ??