Staff Utilization over multiple months

[moriarty]

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!

[daxer-almighty]

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

[daxer-almighty]

It's obvious what's wrong. The message says it clearly: too few arguments. DIVIDE takes 2 or 3 arguments and you have passed just one. https://dax.guide/divide 

By the way, your DAX is not according to the standard. Please read this: Rules for DAX Code Formatting - SQLBI

[moriarty]

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?