1 Reply Latest reply on Jan 19, 2016 2:01 PM by Andrew Watson

# Display aggregate data outside of rows

Hello.

Given the following table (also attached):

 Hospital Region Patient Count Level A A 1 Hospital B A 2 Hospital C A 3 Hospital D A 1 Hospital E A 4 Hospital F A 4 Hospital na A 14 Region G B 2 Hospital H B 2 Hospital I B 6 Hospital J B 5 Hospital K B 8 Hospital na B 21 Region

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 A A 1 Hospital 35 3% B A 2 Hospital 35 6% C A 3 Hospital 35 9% D A 1 Hospital 35 3% E A 4 Hospital 35 11% F A 4 Hospital 35 11% na A 14 Region 35 40% G B 2 Hospital 35 6% H B 2 Hospital 35 6% I B 6 Hospital 35 17% J B 5 Hospital 35 14% K B 8 Hospital 35 23% na B 21 Region 35 60%

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!