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

    Display aggregate data outside of rows

    Michael Lance

      Hello.

       

      Given the following table (also attached):

       

       

      HospitalRegionPatient CountLevel
      AA1Hospital
      BA2Hospital
      CA3Hospital
      DA1Hospital
      EA4Hospital
      FA4Hospital
      naA14Region
      GB2Hospital
      HB2Hospital
      IB6Hospital
      JB5Hospital
      KB8Hospital
      naB21Region

       

      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):

          

      HospitalRegionCountLevelSum Across Regions% of Region Count
      AA1Hospital353%
      BA2Hospital356%
      CA3Hospital359%
      DA1Hospital353%
      EA4Hospital3511%
      FA4Hospital3511%
      naA14Region3540%
      GB2Hospital356%
      HB2Hospital356%
      IB6Hospital3517%
      JB5Hospital3514%
      KB8Hospital3523%
      naB21Region3560%

       

      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!