Employee Utilization Measure

DAX DAX Calculations
1

HI,

With some help from you guys Im getting there now and i require one more piece of your help.

I now have the dates and times my employees come in through the gate at work, and the expected working times they are expected to be productive.

What i want to do now is measure the expected productive hours against what they actually clock in the SAP system and measure their utilization percentages. the target is a minimum of 85% per day and i want to show that for each employee in a bar graph.

Expected Hours & Minutes Inside (Measure) = The expected times the should show SAP clocking’s for. for example if they clock 8 hours on that day its 100% utilized if they clock 6.8 hours that’s 85% which is the minimum expected target per day.

Im struggling to get the measure correct from the COOIS table for the actual clocked durations in SAP. i created a measure called COOIS Clocking but doesn’t appear to pick up correctly in the table

I then want to then calculate the utilization using the expected working time / Actual clocking using the measure Utilization %

Strata Staff Utilization.pbix (1.9 MB)

Thanks for all your help

Dan

2

Examining your model, it appears that COOIS[Duration] is a text field, not a numerical or datetime field.

So, you need to change that text value to a number of some type if you’re going to perform a calculation on it.

3

HI,

But in my measure i converted it here but getting no results

COOIS Clocking =
CONVERT(
SUMX( COOIS, ‘COOIS’[Duration] *1 ),
DATETIME
)

Take just the 7th Dec 2020 for Khuloud for example in the table i see she has made 7 clocks on SAP on that day totaling approx 6.5 hours and yet she was suppose to work a 5 hour day according to her shift assignment so she would be around 115% utilized that day which would need investigation.

How can i make that show in this table

Capture

Dan

4

@Krays23 Please don’t mind, but there are few things incorrect with what you are doing:

  1. Relationship between Dates and COOIS is incorrect, COOIS[Finished Date] is DateTime and it won’t work
  2. You are slicing by Shift Code which Filters Gate Clocking but your measure works on COOIS

I have created a new column for relationship & Duration:

image
image1537×536 67.9 KB

image
image1536×199 31.2 KB

I have no idea what I have done works or not for you:

Strata Staff Utilization.pbix (1.9 MB)

COOIS Clocking 2 = 
CALCULATE ( 
    SUMX ( COOIS, COOIS[DurationCorrected] ),
    CROSSFILTER ( 'Gate Clocking'[Date], Dates[Date], Both )
)
1 Like
5

HI Antriksh,

Yes many thanks for that, i have managed to convert your measure now to a duration

As i said basically i want to calculate the time in the building (gate clocking’s for that day ) against what each person clocks on (COOIS clocking’s) and get the utilization of that person.

I need that in one table and have placed the measures i need in the tab in the attached PBIX however when i add the expected time in building measure i dont see the right thing i assume the context is wrong could you have a look for me and see if you can get the utilization % measure working?

DNA.pbix (1.9 MB)

Regards

Dan

6

@Krays23 In your Utilization % measure you are still referring to [COOIS Clocking] change it to [COOIS Clocking 2]

Utilization % = 
DIVIDE ( 
    [Expected Hours & Minutes Inside], 
    [COOIS Clocking 2]
)

image
image1314×714 92.1 KB

1 Like
9

Hi @Krays23, did the response provided by @AntrikshSharma help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

1 Like
10

Hi @Krays23, we’ve noticed that no response has been received from you since the 29th of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!