I like to think of Subtotals and Grand Totals (GT) as separate queries (they really are not), it helps me square situations like this. That the GT area is a duplicate of your data at a different level of detail. Another way that I like to think of them is as a separate pipeline, where data travels down the normal processing for the main view, and the data is duplicated for the GT pipeline, and table calculation filters are not applied to the GT pipeline because it is data at a different level. Be advised that the above may not be exactly correct, I do not know the code and there is not specific documentation, this is just my current theory from trial and error, I welcome correction.
One of the effects of this is that the filter shelf does not directly affect the GT. For example, right click a mark in the GT area, and you will not see the Keep only/Exclude options like you will for a mark in the normal view area. This is because Tableau does not have the capability to directly target a GT mark. This is why we came up with all the possible workarounds on http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-3/
Also notice on your last sheet "Moving Order Date Works", the GT is not correct for the data in the view.
The only solution I currently know of that is both dynamic and produces the correct numbers is custom SQL to duplicate the data, as in your part 3 above, this enables full control and exact logic any way you want it.
I hope Tableau invests time into making Grand Totals more directly controllable in the future, eliminating the need for all this workarounds.
Just to close this one out, I did hear back from Tableau Tech Support on this and this behavior is by design.
So was there ever an update or an easier work-around to this? I'm showing the top X using a parameter for a viz, and I'd like the total to follow that selection.
To be thorough, I looked at the Total() and Window_Sum() calculations. Total(), gives me the same thing grand total does (which isn't a surprise) but Window_Sum() also gives me the same result too. I thought window_sum was more of a table calc. I am using an index() and a LessThanParam (T/F) to filter.
It's almost like putting a table-calc on the filter is really just "hiding" rather than filtering. Putting my the LessThanParam (T/F) on the pages shelf (which according to Joe's great "Filter Order of Operations" post is more of an advanced hide than a filter) really is just another 'advanced hide' like the pages shelf.
If you're doing a TOTAL(SUM()) and WINDOW_SUM(SUM()) then yes, they will return the same results, but via different routes. See Total() vs Window_Sum() for many more details on the differences between the two functions.
The way I think of table calcs on the Filters Shelf is that they are "late" filters, in other words they are only being applied after most computations are completed (and are separate from Grand Totals and Subtotals). If you want to have a Grand Total or Subtotal (or a subsequent table calc, or R script) respect a table calc filter, then you'll have to embed an equivalent filter computation inside your calculations.
For example, if I'm filtering for INDEX() <= 10 and my measure is SUM([Sales]), then to make the Grand Total respect the table calc filter I'd use IF INDEX() <=10 THEN SUM([Sales]) END.
One note about the Pages Shelf: Although the Pages Shelf affects the final layout (late in the pipeline), dimensions on the Pages Shelf have the same categorization as dimensions on Rows and Columns when it comes to densification behaviors, and along with other dimensions in the view can cause some interesting aggregations in data blending when the granularity of the blend is coarser than the granularity of the view, so there can be effects earlier in the pipeline.
Thanks for the suggestion. I have attempted the IF THEN but was not able to get the grand total to work. Everything is calculated on Table (Down). Here is an example of what I am trying to do:
It's a public example of what I'm doing at work. Basically States are along the top column and a 'top metric' along the side. The tops X metrics are based on the value of the metric. It's not a big deal. My users were fine with the grand total being all, and I labeled it on my work dashboard.
However I took a step back and attempted to do this by going and filtering the metric itself and choosing top X:
I believe that is the best way to approach this problem. Doesn't work for other types of table calcs, but does for top X table calcs.
4 of 6 people found this helpful
Hopefully you will find pleasure in knowing that we have added the ability to apply table calc filters to totals in a single-click in 10.3
Just click on your filter and choose the option to "Apply to Totals"
my company just upgrade to 10.2 and don't think they gonna install the 10.3 anytime soon...is there any other work around? Thanks
THANK YOU! Didn't notice this change and it really does the trick
I'm afraid this is not working for me
I have a Table calc on the Filter by name "Admit Window" and the view correctly filters out the records I need (i.e. when Admit Window < 60 ). However, the Paid Charges or No. of ReAdmission don't seem to be adding up correctly in the Grand Total / Sub-Total (as circled in Red) even when "Apply to Totals" is turned on in the Filter shelf.
I am 10.3.1 and the "Apply to Totals" is not working to me neither. It is pulling the totals neglecting the filters applied to worksheet. Please help if there is anything else to be taken care along with selecting "Apply To Total" on the filter filed.
I have a filtered index and when I click on apply to totals the total doesn't change or if I change compute using, the total disappears. am I doing something wrong?
I am also facing the same issue. Please let me know if you find any solution.
It seems to be applying successfully to 'row grand totals', but not 'column grand totals'
Same with me, the total disappears when clicking 'Apply to Totals'