Given the following table (also attached):
The sum of counts for all hospitals in region A is actually equal to 14. The reason for 'patient count' being 14 for the region is that hospitals A and D share the same patient, so one of the 1s is not used so the answer is not 15, as one might expect.
The sum of counts for all hospitals in region A is actually equal to 21. The same logic applies; hospitals G and H share the same patients so the 2 is only used once in the sum.
I'd like to refer to the sum of actual counts for Regions A and B (14 and 21, respectively, summing to 35) to divide the count per hospital and region by those respective values to get a table that looks like this (only without the regions):
|Hospital||Region||Count||Level||Sum Across Regions||% of Region Count|
The trouble is, when I display it, I cannot seem to display all hospitals at once while still getting the denominator, even with a LOD expression.
I'm convinced that Tableau cannot handle this problem without reshaping the data externally. I really hope someone can prove me wrong!
Thanks in advance!
Have you tried creating a calc saying IF Hospital = 'na' THEN Count END? Then do your window_sum(sum([Count])) to give the Sum Across Regions?
Note I don't have Tableau on this machine and am basing this suggestion purely off the screenshots provided without any insight into the underlying data.