Forum Discussion

moriarty's avatar
moriarty
Frequent Visitor
5 years ago

Staff Utilization over multiple months

I'm a PBI newbie so there may be an easy solution to my utilization rate question but I'm totally stumped.

 

I have three tables I'd like to use in my utilization calculation

 

Resource

Resource_ID

Resource_Name

Resource_Type

 

Time

Resource_ID

Date

Hours_Worked (number)

Billable (Billable/Non Billable)

 

CALENDAR (created with table formula)

Date

Work Hours - calculated with IF(WEEKDAY('Calendar'[Date], 2)< 6,8,0)

 

I've created table relationships between Resource_IDs and Date.

 

I'd like to create a Utilization % rate that calculates (Billable Hours/Total Work Hours) that takes in account slicer filtering on the report page for one or more months.

 

 

The solution I tried was creating two new measures in my Time Table:

 

Total Billable Hours = IF(Time[Billable]="Billable",Time[Hours_Worked],0)
 
Total Hours = SUMX('CALENDAR','CALENDAR'[Work Hours])

 

Then to calculate the Utilization rate, I created the measure:

Utilization % = DIVIDE(Time[Total Billable Hours]/Time[Total Hours])

 

I end up with an error message on my Utilization column that "Too few arguments were passed to the DIVIDE funtion".  Is this because there are some resources on some days that had no Total Billable Hours?  Is this just the completely incorrect way to go about this?

 

Thank you for any replies and suggestions!

 
 
 
 
 
 

3 Replies

    • moriarty's avatar
      moriarty
      Frequent Visitor
      5 years ago

      Hi daxer-almighty,

       

      Thank you for the feedback.  I will read the rules for DAX Code Formatting and adjust my DAX.

       

      Do you have a solution measure for my desired formula?

  • daxer-almighty's avatar
    daxer-almighty
    Solution Sage
    5 years ago

    moriarty 

     

    I have already told you why you get an error in your last formula.

     

    In DIVIDE(Time[Total Billable Hours]/Time[Total Hours]) Time[Total Billable Hours]/Time[Total Hours] is just one number.

     

    But I can't help you because your DAX is not readable. For instance, nobody can tell from your expressions if something is a column or a measure. Please read the guide to DAX formatting and stick to the rules religiously.