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

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!

• ###### 1. Re: How to perform a calculation of a sub-group before the total medium

Good morning

I would prefer to work with the data set and a twbx workbook -

the first part can be resolved by suing LOD's something like {fixed Physician:sum(numerator)} /{fixed Physician:sum(denominator)}

you may have to play with the syntax

and then creating the second chart at the physician level - using the lod in the median function

without seeing the data I dont know where the numerator and denominator come from - are they data or a calculation?

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: How to perform a calculation of a sub-group before the total medium

Thanks, Jim! Your solution worked like a charm

• ###### 3. Re: How to perform a calculation of a sub-group before the total medium

Thanks

Please mark my response to close the thread and so others can find a solution

Jim