4 Replies Latest reply on Nov 5, 2010 11:22 AM by guest contributor

# Calculating  percentage totals on a table including measures that have been excluded from a filter

Maybe the best way to explain this is to offer an example:

I have 4 values a,b,c,d... a percentage total on all 4 values will sum to 100%. if i filter out b, i want the percentage total to stay the same as it was for a,b,c,d...i can't simply hide b, because my grand total still shows 100% and i want it to show 75% (assuming even proportions).

I hope this is clear...any ideas?

thank you!

Kemi

• ###### 1. Re: Calculating  percentage totals on a table including measures that have been excluded from a filter

Name, Value

a, 25

b, 25

c, 25

d, 25

There are a few options depending on what type of interaction your want to allow. One option is a series of calculated fields:

a:

`IIF([Name]="a",[Value],NULL)`

b:

`IIF([Name]="b",[Value],NULL)`

c:

`IIF([Name]="c",[Value],NULL)`

d:

`IIF([Name]="d",[Value],NULL)`

Then you can create other calculated fields like:

Percent a:

`SUM([a])/SUM([Value])`

Percent b:

`SUM([b])/SUM([Value])`

Percent c:

`SUM([c])/SUM([Value])`

Percent d:

`SUM([d])/SUM([Value])`

for filtering out a single "Name":

Percent non b:

`SUM(IIF([Name]<>"b",[Value],NULL))/SUM([Value])`

As for using a quick filter to select what field is not displayed, the addition of parameters in Tableau 6 would allow you to filter out a single "Name", but if you wanted to have a multi select quick filter (or not use calc fields like above), I would recommend adding another column to your data source, either by preprocessing or with custom SQL.

• ###### 2. Re: Calculating  percentage totals on a table including measures that have been excluded from a filter

thanks again Joe for all your help. I tried doing this especially

Percent non b: SUM(IIF([Name]<>"b",[Value],NULL))/SUM([Value])

the returned value is 1  not the percentage of a/sum(a,c,d), c/sum(a,c,d), etc

any ideas why its not computing correctly?

thanks!

• ###### 3. Re: Calculating  percentage totals on a table including measures that have been excluded from a filter

I am trying to do the same thing with a top 10 filter...

I want to only show the top 10 measures of a set but I want their % of total to be on the entire set.

Any help on this would be appreciated.

• ###### 4. Re: Calculating  percentage totals on a table including measures that have been excluded from a filter

If I understand what you are trying to do, you can do this by using HIDE instead of EXCLUDE. You also need to click on 'add all subtotals' under Table. The annoying thing is that each time you change a dimension, you need to do the 'add all subtotal's again as well as having Tableau calculate the percent.