2 Replies Latest reply on Feb 5, 2019 11:53 AM by paul.winiecki

Using a Filtered Column Grand Total in a Calculated Field

Hello,

In Tableau 2018.3 I'm trying to create a column that is the column grand total after the two filters have been applied (Sale = True AND Sugar Cone, Cup, Waffle Cone = Sugar Cone OR Cup) grand column total = 21. I need this because I have to be able to calculate the percentage using the Counts of Ice Cream Flavors and this Column Grand Total. The problem is that I can't use a table calculation such as WINDOW_SUM because there are cases where I will be showing only the top 2 ice cream flavors and thus WINDOW_SUM would a total that was less than 21. I also can't use Sale and Sugar Cone, Cup, and Waffle Cone filters as context filters for reasons that it would not allow me to  correctly use dashboard action filters with a different sheet (I created mock data for this post, so I can't really get into details using my actual data). I tried creating a fixed column grand total, but unfortunately it is affected by the filters not being context filters. For instance I would like all ice cream flavors to have "Column Grand Total" of 21 and then divide the Count for that ice cream flavor by 21, but depending on it an ice cream flavor is sugar cone, cup, or waffle cone it changes the column grand total value. I've attached the package workbook showing a sheet with the correct filters applied along with a sheet showing the data without any filters.

Let me know if you have any solutions or need me to clarify anything. Any help is greatly appreciated.

Thanks,

Paul

• 1. Re: Using a Filtered Column Grand Total in a Calculated Field

Actually, you can use window_sum.

In the attached I made a calc for window sum.  Another one for ranks of SUM(Count).

And then I made a filter to show the top 2.

In the attached I have the window sum and the rank displayed on the sheet.  There is also a [Show top 2] calc on the details shelf.

If you drag the top-2 calc to filters and select for value = 1, (look at the calc and you'll see what it does), you'll see the top 2, and your window sum value remains at 21.

That's because a table calc used as a filter doesn't remove non-displayed rows.  The rank=3 and 4 rows are still in the underlying table.

• 2. Re: Using a Filtered Column Grand Total in a Calculated Field

Perfect this is exactly what I needed. Thanks!

Paul