Ever notice how Sub Totals and/or Grand Totals in Tableau don't always equal the total amount displayed? This happened to me this week while I was working with a table where I was looking for the INT of a set of values. I wanted the Sub Total to display the total amount of the INT values displayed above, not the INT value of all the non-INT values that made up the displayed values above. I remember running into and correcting this issue before, but had forgotten the proper approach to take.
What to do? Peruse the Tableau forums, of course. This exercise led me to some awesome work by Tableau Zen Master Jonathan Drummey which he posted on his blog - Drawing with Numbers. Jonathan goes to great lengths to describe what's going on and how to get around the issues that it takes 3 separate blog posts to complete. I found myself caught in the Category Catch 22 as mentioned in Part 2. Changing my formula up a bit and including a duplicate category field in the Level of Detail shelf did the trick.
If you are experiencing issues with sub totals and grand totals not calculating properly, take a look at the following links:
http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-1/
http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/
http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-3/ (SQL approach)
If you want to watch him explain and work with sub / grand totals, be sure to check out the video at https://tableausoftware.webex.com/tableausoftware/lsr.php?AT=pb&SP=EC&rID=34516302&rKey=4adc0a78232e5f64.
Thanks, Jonathan. You are truly one of the Tableau Pros that Tableau users need to get to know.
Thanks, Andy!
ReplyDelete