topic Re: Calculating utilization percentage based off 8 hour workday in DAX Commands and Tips

Calculating utilization percentage based off 8 hour workday

rachaelwalker — Tue, 01 Jun 2021 15:41:49 GMT

I am trying to calculate the percentage of utilized hours based on a 8 hour work day. The formula I am using though is returning the same percentage for every record. Is there another formula I could use? If works fine for my billable hours which is a native fields in the table.

Utilized Hours is a calculated column based on data from another table.

Utilized Hours = VAR __WorkTypeID = [TimeEntries.workType.id] VAR __UtilizationFlag = MAXX(FILTER('WorkTypes',[WorkTypes.id]=__WorkTypeID),[UtilizationFlag]) RETURN IF(__UtilizationFlag,'TimeEntries'[TimeEntries.actualHours],0)

Utilized %

Utilized % = DIVIDE(sum(TimeEntries[Utilized Hours]),countrows(summarize('TimeEntries','TimeEntries'[TimeEntries.timeStart],'TimeEntries'[TimeEntries.member.id]))*8)

Results

Re: Calculating utilization percentage based off 8 hour workday

krishna0 — Tue, 01 Jun 2021 17:15:19 GMT

Hello,

You're using DIVIDE(SUM()) - this will give you the exact result you expected, one stable value for whole column.

If you need to have only utilized % per one row (as I assume, correct me if I am wrong) out of utilized hours and assuming the 8 hour day wouldn't it be easier to simply DIVIDE([Utilized Hours], 8)?

Best regards,

Re: Calculating utilization percentage based off 8 hour workday

rachaelwalker — Wed, 02 Jun 2021 17:13:07 GMT

We want to see daily utilization for individual employees and for all employees per day based on everyone works an 8 hour day... My original formula is working for my billable hours so I dont understand why it is not working for my Utilized hours. They only difference between billable hours and utilized hours, is I can see billable hours in power query but not utilized (sorry still learning). I also added your formula

Re: Calculating utilization percentage based off 8 hour workday

rachaelwalker — Wed, 02 Jun 2021 19:40:39 GMT

Another issue using my formula for Utilized Hours is the line graph is flat line. Billable reflects properly using same formula. Any ideas?

Re: Calculating utilization percentage based off 8 hour workday

rachaelwalker — Tue, 08 Jun 2021 20:02:45 GMT

I was able to get it to work properly using my original formula. I was using a calculated column when it should have been a measure. User error. =X