Forum Discussion

lamysroe's avatar
lamysroe
Icon for Advocate I rankAdvocate I
6 years ago
Solved

Using filters in measures to determine utilization

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)

 

 

 

Need Help
  • lamysroe's avatar
    lamysroe
    6 years ago

    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])

     

     

3 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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

  • lamysroe's avatar
    lamysroe
    Icon for Advocate I rankAdvocate I
    6 years ago

    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])