Percentage of MCR by financial class not working

[BaldAccountant]

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=f41yo7wstgz53wv8lg1cgi...

[BaldAccountant]

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

[eliasayyy · Accepted solution]

please try 

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

[eliasayyy]

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% ...