The calculation you highlighted doesn't add up. 567,770/24,220 = 23.44, not 187.5 or 175.
My suggestions are:
- Remove PCU (Orig) from the view, or make it an ATTR(). That way effects on the table calc will be minimized. This can also speed up Tableau, because Tableau is retrieving fewer dimensions and potentially fewer rows from the data source.
- Put COUNT(Beds (based on new PCU)) on Measure Values and validate that it's returning what you expect. (I'm looking at the fields in the Bed Count v01 formula).
- Put COUNTD(PCU (new)) on Measure Values and validate that it's returning what you expect.
I'm guessing that one of those calcs is returning different results in the Grand Total, but I'm not sure which, and it might be the calc that is inaccurate.
Also, whenever asking for help about table calcs and not supplying a packaged workbook, you must let us know what the settings are for addressing/Compute Using, At the Level, and Restarting Every. Without those, then we're not sure what the table calc is actually computing to generate the results.
567,770/24,220 = 23.44 ... * 8 = 187.5 (forgot to add the * countd(PCU)) .. my bad.
I've cleaned out the sensitive data and attached the packaged workbook.
The goal is to get the subtotal of 175 and a Grand Total of 367 (I've seen your blog post on Totals in 8.1, but it doesn't seem to apply to subtotals)
I got the # of Beds to work using the new Grand Total features in v8.1 (changed total to Total using SUM) ...
But now the Occupancy calculation doesn't seem right (total using Average):
158.33/175.00 = 90.47% .. but Tableau shows 88.31%
Ok, I can see where this is a bit challenging...the Max Beds calc is at a higher level of detail, then you're bringing it down into the lower level of detail for the Census, then the subtotals and grand totals do their own computations at a higher level of detail and somehow we have to get everything to work in the same view.
For the Max Beds, you're using the aggregation MAX(Max Beds) with a two-pass total using Sum, which means what you're seeing in the subtotal row for Max Beds is effectively WINDOW_SUM(MAX(Max Beds)). That is not the calc being used for Occupancy, so that's why the subtotal Census/subtotal Max Beds doesn't line up.
The Occupancy calc is a row-level calc using Census/Max Beds, and has the SUM() aggregation applied. So what it's doing is summing up all the row-level Occupancies to get the final rate, and because of how your data is laid out that works ok in the detail rows, but the calcs can break in the total rows because those computations are occuring at a higher level of aggregation. Also, when you are creating the Subtotal using the Average two-pass total, that's not computing a true average, that would be something a little different, the average is the average of all the Occupancy values.
I solved this in the attached workbook using the duplicate dimension technique outlined in http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/. That way we have the level of detail inside the subtotal computation.
To be more explicit about what Tableau is doing: The two-pass totals are *only* for the display of the total and subtotal rows, they are not used in other calculations. So if you want to use the value of a two-pass total in another calc, you have to ignore the two-pass total and build your own calculations that return those results, which requires awareness of the level of detail in the detail rows and subtotal/grandtotal computations.
[woops, forgot the attachment]
Was there suppose-to be an attachment? I'll re-read the blog post for a third time
Thanks ... it will take a couple of hours and maybe a good night sleep to get my head around this one.
Sorry about that, you should see it now.
On Tue, Nov 26, 2013 at 3:16 PM, Jason Scarlett <