3 Replies Latest reply on Jul 28, 2015 9:18 AM by kiran.pinnamaneni

How to get percentage of total (unfiltered data) after a filter?

Hi, all:

I am trying to compute percentage of total after applying a filter, using attached coffee chain dataset as an example. So in the first sheet "% among all states", the label number shows exactly the right number.  But after I applied a filter (top 5 margin states in 2011), the results turn to be % of margin among 2011 top 5 margin states. Is there a way I can still get the number as shown in sheet 1?

Or maybe other way, how to create a new field that dynamically groups states by 2011 margin, and hence show something as in sheet 3 "Top 5 vs. Others - how to make grouping dynamic?" ?

Thank you very much in advance!

Best,

Li

• 1. Re: How to get percentage of total (unfiltered data) after a filter?

Instead of filtering on state, I recommend you to use INDEX as a filter. TOTAL() is a table calculation, and it is a very powerful feature of Tableau. The reason why it is called table calcs is because the calculations are done based on the view. When you filter on state, the view changes. As a result, your table calc will break.

1. put a discrete INDEX on rows, in front of state

2. put a continuous INDEX on filters card, show quick filter and

3. select "range of values" as filter type

now you can filter down to top 5 without breaking the calculation.

• 2. Re: How to get percentage of total (unfiltered data) after a filter?

Hi, Jazmyn:

Thank you very much! Yes, it works!

I have a second question: In the attached workbook based on yours, I tried to create a monthly margin stats for top 5 states in 2011. The INDEX() and % of total all seem work all right. But is there a way to remove the partition line between 2010 and 2011 panes so it will connect continuously? Appreciate your time!

Best,

Li

• 3. Re: How to get percentage of total (unfiltered data) after a filter?

Hi Li / Jazmyn,

Did you figure out how to plot Top N on a line graph without impacting the percentage values ? Attaching the work book. Appreciate your help.

Thanks,

kp