3 Replies Latest reply on Apr 2, 2019 2:22 PM by Jim Dehner

    How to perform a calculation of a sub-group before the total medium

    Lily Nguyen

      Hi there! I am having issues performing a calculation for a sub-group before performing the median calculation on the whole population. Here is what I mean.

       

      Take the table below as an example of my dataset. I am calculating metrics for each physician (A, B, C, D, E). Some of them work in more than 1 hospital (e.g. physician A works at hospital 1 and 2). The metric is to take the numerator and divide it by the denominator to calculate the value for each physician, and then calculate the median. However, for physicians that work in more than one hospital, I need the numerator to be summed first and then divided by the sum of denominator for the same physician (e.g. physician A would have numerator = 4 divided by denominator = 13). That number is then taken as the value for that physician before the mean for all physician is calculated.

       

       

      Dataset:

          

      PhysicianIDHospitalIDNumeratorDenominator
      A116
      A237
      B249
      C325
      D1510
      D2110
      D3410
      E259

       

       

      Desired values: Median should be 40%

       

        

      PhysicianIDCorrect Value
      A31%
      B44%
      C40%
      D33%
      E56%
      Median40%

       

       

      What Tableau is giving me: Median is 41%, which is wrong

        

      PhysicianIDIncorrect Value (row level instead of physician level calculation)
      A17%
      A43%
      B44%
      C40%
      D50%
      D10%
      D40%
      E56%
      Median41%

       

       

      I cannot attached a packaged workbook as the information may be sensitive and there's a lot of data, but i can create a smaller dataset from the actual data if really necessary. I am using Tableau desktop version 10.0.

       

      Please help

       

      Thanks in advance!