cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DataDiva Regular Visitor
Regular Visitor

Help with calculation

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
DataDiva Regular Visitor
Regular Visitor

Re: Help with calculation

I figured out the problem! It had to do with having a one-way filter relationship instead of a cross-filter (two-way) relationship. As soon as I fixed that, the measure calculated correctly. Thank you!

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Help with calculation

Hi @DataDiva,

 

Based on your description, I have known your desired output.

 

If it is convenient, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DataDiva Regular Visitor
Regular Visitor

Re: Help with calculation

I figured out the problem! It had to do with having a one-way filter relationship instead of a cross-filter (two-way) relationship. As soon as I fixed that, the measure calculated correctly. Thank you!

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors