Matrix - Calc the % in a column, off that of another column's single row item - Thanks

[tbobolz]

I have a simple matrix, it lists insurance payor groups in the rows. (Commercail, Medicare, Self Pay, etc..., all in the data field titled "Financial Class Rollup") then a 2nd rows field for actual payer. (So when expanded, the Commercail row will expand to Blue Cross, Aetna, Farmers, etc. form a data field titled "Insurance Description")

In the columns I have data fields: "Charge", "Payment Amount" and "Realization Rate" as a measurement (Payment Amount/Charges).

I have been struggling with creating another mesurement percentage column called "% of Medicare". This field would take the above calculated percent from each row (expanded or not) of the realization rate column and divide it by ONLY the "Medicare" pecentage of the same realization rate column. 

I thought i had it with this formula, but it fails on drill down. 

% of MCR (Sole) = [Realization Rate]/CALCULATE(sum('Realization Rates'[Payment Amount])/sum('Realization Rates'[Charge]), 'Realization Rates'[Insurance Plan 1 - Financial Class Rollup]="Medicare")

I have added a sample file with sample data. The measurement "% of MCR (Sole)" does exactly what I would like it do do, except when I expand down one hierarchy. I'm not skilled enough at DAX to write this.

Sample pbix 

Thank you for any help you can offer.

Terry

[parry2k · Accepted solution]

@tbobolz here is your revised measure

% of MCR (Sole) = 
DIVIDE ( 
    [Realization Rate],
    CALCULATE ( 
        [Realization Rate],
        ALLSELECTED(), 
        'Realization Rates'[Insurance Plan 1 - Financial Class Rollup] = "Medicare"
    )
)

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[tbobolz]

How does one give Kudos? 

[tbobolz]

Thank you! I was playin gwith the divide command, but did not get it correct.

Much appreciated.

Terry

[parry2k]

@tbobolz is this what you are expecting?

[tbobolz]

Yes, i believe that is it!

Great, excited to learn.

Terry

[parry2k]

@tbobolz it will easier if you paste the sample data with the expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[tbobolz]

Thanks Parry, I have added a sample file with sample data. The measurement "% of MCR (Sole)" does exactly what I would like it do do, except when I expand down one hierarchy. I'm not skilled enough at DAX to write this.

Sample pbix 

Thanks agian.