Using filters in measures to determine utilization

[lamysroe]

Hi - I am looking to get utilization rates of many rooms. I have the basic formula of duration/works hours, but I am having issues getting a formula that works with the filter selection. 

So... When I select multiple conference rooms, I need the formula to account for this and take my available work hours and mulitply it by X# of rooms selected. 

This is the formula I have comeup with but it doesn't work due to SUM restrictions. 

Utilization Combined = DIVIDE(SUM('Meetings'[Duration]),SUM('Calendar'[Work Hours Actual] *FILTER(Meetings,Meetings[Room])),0)

[lamysroe · Accepted solution]

I am using a many to one relationship with the date of the meeting (many) to the date on the calendar (one). With that being said, I figured it out. 

I used existing column Duration 

I made a new measure:   CalcWorkHours = SUM('Calendar'[Work Hours Actual Yearly]) * DISTINCTCOUNT(Meetings[Room])

DISTINCTCOUNT accounts for the number of meeting rooms selected in the filter. From there I creatd another measure.

Utilization = DIVIDE(SUM('Meetings'[Duration]),[CalcWorkHours])

[v-chuncz-msft]

@lamysroe 

You might need to use SUMX function.

[az38]

Hi @lamysroe 

its difficult to create solution without data model

whats relationships have your Calendar table?

what do you mean with this statement?

FILTER(Meetings,Meetings[Room])

 multiplier 

SUM('Meetings'[Duration])

will give you sum of ALL meeting duration from your data model.

so, the better way - give us example of your data tables for help

do not hesitate to kudo useful posts and mark solutions as solution