cancel
Showing results for
Did you mean:
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:

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

2 REPLIES 2
Community Support Team

## Re: Help with calculation

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

Announcements

#### 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!

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?

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

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