Calculate employee utilization

[BCMar]

I am pretty new to the scripting piece of PowerBI and need to solve the following problem:

I track certain customer visits of my team using an XLS file. This file contains the date of the visit, the customer name, a whole bunch of other information and also the duration of the visit.

I want to track now the "utilization" of every employee by calculating:

Sum of all durations of the employee/ possible work hours of the employee (in a certain time frame like week, quater, year)

Now here comes the issue: There are weeks were an employee doesn't have a visit at all. And that breakes my calculation. 

Here is what I have so far:

Measurement=((SUM(Responses[Duration [hrs]]])+Sum(Responses[Prep Time]))/Sum(WorkHrsIndividual[WrkHrsTotalIndividual])*100)

Responses[Duration [hrs]] -- Duration of the customer visit

Responses[Prep Time] -- Preperation time for the customer visit

WorkHrsIndividual[WrkHrsTotalIndividual] -- this is a separate table containing the total work hours of an employee based on his start date in our company: WrkHrsTotalIndividual = (((WEEKNUM(TODAY())-WEEKNUM(StartDate)))*40)

I guess my issue is with this work hour calculation. The work hours stay static if I dynamically navigate through my report. The durations change as they should...

Hope someone can give me a hint on how I can make the calculation of the work hours dynamic in my report...

[v-qiuyu-msft]

---

Now here comes the issue: There are weeks were an employee doesn't have a visit at all. And that breakes my calculation. 

Here is what I have so far:

Measurement=((SUM(Responses[Duration [hrs]]])+Sum(Responses[Prep Time]))/Sum(WorkHrsIndividual[WrkHrsTotalIndividual])*100)

---

Hi @BCMar,

Which result do you want to return if the employee doesn't have a visit at all? From the DAX, there should have two tables Responses and WorkHrsIndividual, please share some dummy data within these tables and clarify the corresponding expected results.

Best Regards,
QiuyunYu

[TomMartens]

Hey,

it would be really helpful if you can prepare a sample pbix file and make this downloadable via onedrive or dropbox, besides yyour really good explanation.

Regards