Which person has worked Overtime and how many times within a certain timeframe?

[acg]

Hi, 

I like to solve the following problem:

In a company some employees have worked more than 10% overtime. The task is to monitor who is consistently working overtime over the past 6 weeks and how often. 

Below is some example data. 

There is a time variable: last day of week, the Employee ID, the overtime done in PCT, and I worked out an OvertimeCount:

OTCount = IF([Overtime Pct]>=10,1,0) to indicate employees that have worked over 10% -  ideally that would be a slicer that allows me to chaoose the Percentage (10% or 20%), but I think I would need to do different counts for that, one for 10% and one for 20%.

More pressing though, How can I create a slicer that shows me all employes that have worked 2,3,4 etc times over time (based on 10% overtime or 20% etc) 

I think the OTTotal should help, yet I cannot put this measure into a slicer:

OTTotal =

VAR _Total = SUMX(VALUES('Timesheet Data'[Employee ID]),[OTCount])

RETURN IF(HASONEVALUE('Timesheet Data'[Employee ID]),[OTCount],_Total)

How can I showcase how many and which employee has worked overtime more than twice within a certain timeframe?

last_day_of_week	Employee ID	Overtime Pct	OTCount	OTTotal
20/06/2021	12	2.8	0	0
27/06/2021	12	32.6	1	1
4/07/2021	12	7.6	0	0
11/07/2021	12	26.4	1	1
18/07/2021	12	20.8	1	1
25/07/2021	12	28.5	1	1
11/07/2021	22		0	0
18/07/2021	22		0	0
25/07/2021	22	0.0	0	0
1/08/2021	22		0	0
20/06/2021	47	5.8	0	0
27/06/2021	47	1.3	0	0
4/07/2021	47		0	0
11/07/2021	47	1.3	0	0
18/07/2021	47		0	0
25/07/2021	47		0	0
27/06/2021	55		0	0
4/07/2021	55	5.0	0	0
11/07/2021	55		0	0
18/07/2021	55		0	0
25/07/2021	55		0	0
20/06/2021	6	11.3	1	1
27/06/2021	6		0	0
4/07/2021	6	12.5	1	1
11/07/2021	6		0	0
18/07/2021	6		0	0
25/07/2021	6	11.3	1	1
20/06/2021	35	0.6	0	0
27/06/2021	35	0.6	0	0
4/07/2021	35	1.9	0	0
11/07/2021	35	23.8	1	1
18/07/2021	35	2.5	0	0
25/07/2021	35	11.9	1	1
20/06/2021	42	8.8	0	0
27/06/2021	42	8.8	0	0
4/07/2021	42	6.9	0	0
11/07/2021	42	5.0	0	0
18/07/2021	42	5.0	0	0
25/07/2021	42	16.3	1	1
1/08/2021	42	6.3	0	0
20/06/2021	11		0	0
27/06/2021	11	25.0	1	1
4/07/2021	11		0	0
11/07/2021	11	25.0	1	1
18/07/2021	11	6.3	0	0
25/07/2021	11	25.0	1	1
1/08/2021	11		0	0
20/06/2021	27		0	0
27/06/2021	27		0	0
4/07/2021	27		0	0
11/07/2021	27		0	0
18/07/2021	27		0	0
25/07/2021	27		0	0

[ERD · Accepted solution]

Hi @acg ,

Assuming there are measures (took from your previous post):

OT = 
VAR currentEmpID = MAX ( T[Employee ID] )
VAR currentLDW = MAX ( T[last_day_of_week] )
VAR currentContractHours = MAX ( T[Contract_Hours] )
VAR OT =
    CALCULATE (
        SUM ( T[Hours] ),
        FILTER (
            ALL ( T[Employee ID], T[last_day_of_week] ),
            T[Employee ID] = currentEmpID
                && T[last_day_of_week] = currentLDW
        )
    ) - currentContractHours
RETURN
    IF ( OT > 0, OT )

OT% = 
VAR currentContractHours = MAX ( T[Contract_Hours] )
VAR OT = [OT] / currentContractHours
RETURN
    OT

OT>10% = IF([OT%] >= 0.1, 1, 0)

Your OTTotal measure can be created this way:

OTTotal = 
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE ( T, T[last_day_of_week], T[Employee ID] ),
        "@v", [OT>10%]
    )
RETURN
    SUMX ( _t, [@v] )

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

[acg]

Hi ERD, strangely enough this does not work. THe measure translates fine, but I can not display it in a table. I also tried to use it in a slicer. 

You are right, it is a follow up question from the previous post and OT, OT Perc and OT>10% are all measures.  So the build up of the variables should not be the problem. 

Any ideas why that would be?  Does the 

"@v"

not need to be introduced?

From what I know , I should be able to place any of the variables below into a slicer to filter on it. Would that be right, as the filter needs to be able to direct the week of choice, the ID as well as how often ID x has worked over time. 

Thank you for your help. 

   SUMMARIZE ( T, T[last_day_of_week], T[Employee ID] ),

[acg]

Should result in something like this:

Employee ID	OTTotal
12	4
22	0
47	0
55	0
6	3
35	2
42	1
11	3
11	0
27	0