topic Re: Percentage of MCR by financial class not working in DAX Commands and Tips

Percentage of MCR by financial class not working

BaldAccountant — Tue, 09 May 2023 14:27:17 GMT

Percentage of Medicare by financial class

I need help with a measure in Power pivot which does not do what I expect it to do.

In healthcare one differentiation we use is a financial class, or insurance type. In my organization we use MCR for Medicare.

I set up a measure as follows:

MCR % Cases:=divide(CALCULATE([Cases],Data[FC]="MCR"),[Cases],0)

It works fine for pivot tables like MCR % for each month or by doctor, etc. But it does not behave the way I would expect for a pivot table by financial class. I would expect Medicare to be 100% and all the others to be zero. Here is what I get instead:

It appears to be dividing the total number of medicare cases in all FC by the number of cases for each financial class (for BC 641/428 or 149.77%), but I would expect that for BC it would be dividing the number of medicare cases in the blue cross financial class by the total number of BC cases, or 0/428 which should be 0.

Is my formula wrong or is my expectation wrong (maybe I am having a brain freeze)?

I tried to attach a link to dropbox, but it does not always seem to work.

Note the data in this spreadsheet is fake, made up.

https://www.dropbox.com/scl/fi/q5v33edzs54jdnd2ddaj5/Test-MCR.xlsx?dl=0&rlkey=f41yo7wstgz53wv8lg1cgizic

Re: Percentage of MCR by financial class not working

eliasayyy — Tue, 09 May 2023 14:53:36 GMT

hello can you explain better your goaal, the % is what exactly all values divided by cases of each FC? or what exactly so lets say BC has 10 , COMM has 15 MCD has 20 MCR has 30 and WC has 25 so total is 100 and BC % is 10% ...

Re: Percentage of MCR by financial class not working

BaldAccountant — Tue, 09 May 2023 14:58:00 GMT

Since there are no medicare cases in BC (all those cases are BC), I would expect the % of medicare cases for the BC financial class to be zero

Re: Percentage of MCR by financial class not working

eliasayyy — Tue, 09 May 2023 15:19:38 GMT

please try

MCR% = IF( CONTAINS(Data1,Data1[FC],"MCR"), DIVIDE( CALCULATE([Total Cases],Data1[FC] = "MCR"), [Total Cases], 0 ),0 )