I'm trying to calculate average occupancy rate for a set of hospitals. The formula I have right now is:
Avg IP Occupancy Rate = 'Calcs'[IP Patient Days]/calculate([Days in Period],ALLSELECTED('vwCHAprimary_Masked'))/AVERAGE('tblFacility'[Licensed Beds])
My problem is that the formula doesn't work if I don't put some kind of aggregation on "Licensed Beds" but when I do put an aggreation, it creates a single aggregation for the whole thing, rather than obeying the row context in the table or graph that I put the measure in. In other words, I'm getting:
Hospital Total Patient Days Days in Period Licensed Beds Occupancy Rate
A 150 365 120.5 wrong
B 350 365 120.5 wrong
C 500 365 120.5 wrong
What I need is:
Hospital Total Patient Days Days in Period Licensed Beds Occupancy Rate
A 150 365 100 Right!
B 350 365 50 Right!
C 500 365 200 Right!
I have four relevent tables--my Primary table, which feeds the detached "Calcs" table, the facility table, and an extended facility table that is generating the Licensed Beds number within the Facility table. I have two facility tables that share an ID, and then one of those tables (the one in the formula) that share a different facility ID with my primary table. So I used Licensed Beds = related('cha tblFacilityExtended'[Licensed Beds]) to create a column in my Facility table that has licensed beds. Confusing, but I wanted to explain in case that is relevent to my problem. My Primary table contains records of every hospital visit, which is where I can calcuate total Patient Days from. I have a filter to select your time period. And then I have these facility tables that list each facility once, with relevent info about each facility, including licensed beds.
Any idea how to get Power BI to behave the way I need it to...? I'm really confused by the fact that I can put an average of other fields, like Patient Days, and that average will respect the row level context. So why isn't Licensed Beds working that way? I've tried using the original licensed beds field in the extended table, but I tried combining it into the regular facility table because I thought perhaps Power BI didn't know to activate the two-level relationship (Primary-->Facility-->Facility Extended)--In fact, if I add in the Facility ID from the Facility Extended Table, the occupancy rate calc works! But I can't include that in my final table, it's a huge 36 Hex charater tag. I clearly don't understand what Power BI is doing here...
