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

    Using a Filtered Column Grand Total in a Calculated Field

    paul.winiecki

      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.

       

      column grand total.jpg

       

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

       

      Thanks,

      Paul