Dividing Two Columns without denominator not changing

[novotnajk]

Hi again!

I have a table that contains a list of patients, called MASTER PATIENT [Patient ID].

I have another table that contains medical diagnoses by patients, date of service, and spend, called MASTER MEDICAL [Diagnosis, [Service Date] and [Spend]

For visual, here is a sample layout:

MASTER PATIENT

Patient ID                    Reporting Date

123456789                  1/1/2019

000000001                  1/1/2019

111111111                  1/1/2019

222222222                  1/1/2019

333333333                  1/1/2019

Here I have 5 patients in January 

MASTER MEDICAL

Patient ID            Diagnosis               Spend          Reporting Date

123456789          Asthma                   $500            1/1/2019

000000001          Cancer                     $24,500      1/1/2019

Here I have 2 patients, 2 diagnoses with a total spend of $25,000.

What I'm trying to do is divide $25,000 by the 5 patients in the MASTER PATIENT file; however, because there is a relationship between the two tables, it is dividing $25,000 by the 2 patients in the medical file. I have a slicer to show claims between 1/1/2019 and 12/31/2019.  So with this slicer, I have 5 patients who in total have spent $25,000.

My formula is the following:

Per Patient Per Year = CALCULATE(DIVIDE('MASTER MEDICAL'[Spend]),CALCUATE(DISTINCTCOUNT('MASTER PATIENT'[Patient ID])

I'm expecting a result of $5,000 per patient per year and not the $17,500 that it is producing (whereas $17,500 is $25,000 divided by the 2 patients in the MASTER MEDICAL file).

Any ideas?  Should I create a copy MASTER PATIENT and not have any relationships to any of the other tables?

[v-lid-msft]

Hi @novotnajk ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft]

Hi @novotnajk ,

We have tested your shared table and measure, but get the expected result 5K.

Per Patient Per Year = CALCULATE(DIVIDE(SUM('MASTER MEDICAL'[Spend]),CALCULATE(DISTINCTCOUNT('MASTER PATIENT'[Patient ID]))))

Could you please provide more details about it?

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nathaniel_C]

Hey @novotnajk ,

Working on something else, but how about just distinct count of the patients, and sum of the cash, then don't calculate, but just Divide(cash,patient count)

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel