Tech productivity analysis

[Soparklion]

My technicians can operate multiple machines at the same time - they are programmable and largely hands-off once they get started.  I want to identify which of my technicians are working at what time and the largest number of machines that they operated over each hour during the day - they get bonuses for productivity.

The raw data has a job number, technician ID, machine start time and machine finish time in an Excel file.  In a separate file, I have the technician daily assignment schedule.

Is there an easy way to analyze this in Power BI?  Do I need to match the assignment schedule from the 2nd Excel file with the time data from the primary Excel file before the Power BI analysis?

Any suggestions are appreciated.

[v-lionel-msft · Accepted solution]

Hi @Soparklion ,

" I want to identify which of my technicians are working at what time"

First, I created two columns:

date = FORMAT([Working time], "mm/dd/yyyy")
period = 
VAR x= FORMAT([Working time],"HH:mm:ss")
VAR y= FORMAT([Off time], "HH:mm:ss")
RETURN
COMBINEVALUES("-", x, y)

 

Then, I created a measure:

Measure 2 = 
IF(
    HASONEFILTER(Sheet2[ Technician ID]),
    VAR x = COUNT(Sheet2[ Technician ID])
    RETURN
    IF(
        x <> 0,
        "T"
    ),
    CALCULATE(
        COUNT(Sheet2[ Technician ID]),
        ALLEXCEPT(
            Sheet2,
            Sheet2[date], Sheet2[period]
        )
    )
)

Then, I got this Matrix:

(used table: sheet2)

"the largest number of machines that they operated over each hour during the day"

First, I created one column:

date = FORMAT([Working time], "mm/dd/yyyy")

Then, I created one measure:

Largest number = 
VAR x =
DATEDIFF(
    MAX(Sheet1[Machine start time]),
    MAX(Sheet1[machine finish time]),
    HOUR
)
RETURN
CALCULATE(
    COUNT(Sheet1[Job number]),
    FILTER(
        Sheet1,
        x >= 1
    )
)
    

(used table: sheet1)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

What do you mean by 'machine start time'? is that just the time or is it a date with a timestamp?

[Soparklion]

I have a date-time stamp for each, as we work an overnight shift at times.  Thank you!

[Anonymous]

I think the easiest way to do this is to add a date table. and link both tables with this date table (in Power BI)

If you don't know what a date table is, or don't know how to create one, this blog probably will help you:

https://kohera.be/blog/power-bi/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/

(note that based on your regional settings, you should use ',' instead of ';' in the dax formula provided.)

Let me know if you have any questions.

[Soparklion]

Thank you for the suggestion.  How can I determine concurrency?