I see that the Sub total values for ( Average of universe) in your workbook are not matching with the values in the excel sheet. I think that should be the root cause to your issue. Try rectifying those values and give a try. Hope its solves your issue.
1 of 1 people found this helpful
My understanding is that since your sub-totals are average, the grand total will also be calculated as an average. In the column for 'Average of Universe' the grand total is the average of 692,737 and 61,178.
In Excel you seem to calculate the sub-totals as average and the grand total as sum. I don't think this is possible in Tableau - please someone correct me if I am wrong?
Since Jonathan Drummey seems to be able to put anything he wants in the Grand Total column, Julia I suspect you are probably, maybe, slightly, sort of wrong. But I have no way of really knowing because I don't possess Jonathan's Grand Total chops. This lack of knowledge on my part is somewhat a lack of remembering how to do all the nifty tricks Jonathan has taught me, and part a protest against the incredibly dense "stuff" we need to learn/remember just to use the product ... a supposedly drag 'n drop product. [I commented just today to a friend that it is ironic that I originally gravitated to Tableau because it was a drag 'n drop data viz engine, but I find myself several years later having just had to learn SQL to provide my client the solution they were looking for.]
Of course this is all about text tables, so I can't really complain about how difficult they are to create, because I really would prefer they went away altogether.
Thanks for the vote of confidence, Shawn! Yes, it is possible to get the desired result, and yes, this is complicated. I’ve come to think of customizations of subtotals & grand totals with the metaphor of solving a Rubik’s cube, in the dark, while wearing thick rubber gloves. We have to know the granularity of our data, the granularity of the view (based on the dimensions in the view), and the different granularities that each subtotal and grand total are working with, only we can’t directly see those for the subtotals and grand totals so we have to imagine them. Then we have to set up a measure that does the right aggregation at each level, with the proper addressing and partitioning of table calculations (since we’re almost certainly going to have to use a table calc). Since we don’t get visibility into each level of computation, we have to be able to imagine the entire computation all at once to get the desired results.
I used a variation on the subtotals instructions from http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/ here. There’s a duplicate of the Program dimension on the Level of Detail, so we can have the Programs available in the Grand Total computation to be able to sum the averages of the Universe values. As I’d noted in the Customizing Grand Totals blog post, all the measures have to be revised to handle that extra level of detail:
IF FIRST()==0 THEN WINDOW_SUM(SUM()) END
IF FIRST()==0 THEN WINDOW_SUM(AVG()) END
All of these have a Compute Using on the Market (copy) dimension, and here’s the result:
To create the view, there were two other steps, covered in http://drawingwithnumbers.artisart.org/customizing-grand-totals-in-tableau-v8-the-stacking-snag/. I Ctrl+Dragged a copy of the TARPS measure from Measure Values onto the Filters Shelf, setting it to filter for non-Null values, and then turned Analysis->Stack Marks off.
Workbook is attached.
Hi Jonathan - That was very insightful. I have an issue that your suggestion half solves it. The grand total on column still doesnt reflect the sum of averages.
I have attached a workbook. It would help a great deal if you could help me with what I am missing.
Test Report.zip 12.4 KB