cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Percentage of MCR by financial class not working

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.

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

1 ACCEPTED SOLUTION
Super User

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

3 REPLIES 3

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

Super User

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.