cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

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.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors