11 Replies Latest reply on Mar 15, 2017 11:47 AM by Gerardo Varela

# When filter changes, display the actual total based on selection

When changing the filter selection, (e.g., only A and B are selected), how to make the Grand Total row displayed as 12, 66.66% for FEB14? The sample workbook is attached. Thank you for your help!

• ###### 1. Re: When filter changes, display the actual total based on selection

Hi Kate

Can't explain why but I took the totals off then recreated the view and it came back properly - I used the filters to change the combinations and it seemed to work

I used t 10.2 so I can send it but see below

Hope it helps

Jim

• ###### 2. Re: When filter changes, display the actual total based on selection

That doesn't capture the essence of what Kate needs to do.

There is a table calc in the viz -- Percent of total quick table calc.  So all the underlying rows need to remain in the table.  So she has a table calc filter to display just the rows requested.  In the example, two of the rows each have 33% of the total.  that's what she needs to see.  But the Grand Total still adds up the whole table -- as Tableau is designed to do.  So she wants a way to sum only the values displayed.  (In this case it should add up to 66%.)

I'm trying to find a way to tap into the LOOKUP calc in the filter to sum up only those rows that satisfy the filter, but I can't find a way to do that yet.  (Or a way to rewrite the filter so that we CAN capture what is selected.)

If I can figure that out, then we can write a WINDOW_SUM that excludes what the user doesn't want to see, and create a "partner" sheet that contains those totals, and then position the two sheets on a dashboard to look like one report.

1 of 1 people found this helpful
• ###### 3. Re: When filter changes, display the actual total based on selection

Thanks Joe - I clearly didn't understand that - its much clearer now

Jim

• ###### 4. Re: When filter changes, display the actual total based on selection

Hi Katie,

You could use LODs.

Percent

Then just use a regular quick filter on grades.  This will get you closer. Although, I'm not sure why I'm seeing 66.67, though.

I believe it has something to do with IEEE 754 floating-point standard but I can't prove it.

Regards,

Gerardo

1 of 1 people found this helpful
• ###### 5. Re: When filter changes, display the actual total based on selection

Hi All

Apologies to all. Joe's right I completely missed the point of the question.

See the attached 10.1 - the worksheet illustrates Joe's point - the difference between taking totals and percentages based on what is in the filtered view and what is in the underlying table.

The first 2 columns in each date band represent what is in the filtered view - the remaining 2 are what is in the underlying table

This is how the view changes when the filter is set to ALL and the filtered view and underlying table align

Jim

1 of 1 people found this helpful
• ###### 6. Re: When filter changes, display the actual total based on selection

Sorry hit send too soon

you needed the fomulas

Jim

• ###### 7. Re: When filter changes, display the actual total based on selection

Right Jim -- I never figured out a way to make this work solely with table calcs.

LODs make it work, though, and you've done a nice workable solution.

• ###### 8. Re: When filter changes, display the actual total based on selection

Thanks Joe

Jim

On Sun, Mar 12, 2017 at 5:31 PM, Joe Oppelt <tableaucommunity@tableau.com>

• ###### 9. Re: When filter changes, display the actual total based on selection

Both Gerardo and Joe's methods work! Thank you so much!

• ###### 10. Re: When filter changes, display the actual total based on selection

Thanks Kate

Sorry I missed the point the first time around but glad I was able to help

Jim

• ###### 11. Re: When filter changes, display the actual total based on selection

You're welcome!

Regards,

Gerardo