Forum Discussion

Soparklion's avatar
Soparklion
New Member
6 years ago
Solved

Tech productivity analysis

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.

 

 

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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.

5 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    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's avatar
    Anonymous
    Not applicable
    6 years ago

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


    • Soparklion's avatar
      Soparklion
      New Member
      6 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.