New field calculation based on Matrix Sum

[kaydee64]

Hello,

I am currently creating a report to give employees an overview of their worked hours including overtime during the month.

I would like to include a measure that is using the sum of my table to make another calculation.

My table looks like this:

Day of Month |	hours (contract) |	hours worked |	overtime
1	8	9	1
2	8	7,5	-0,5
3	8	10	2
...	...	...	...
Total	168	170,05	2,5

Overtime is a measured field subtracting column C and B.

Some people have overtime included in their contract. For example they are supposed to work 40 hours a week and 2 hours of overtime per month are included (overtime is only accredited if it is above 2 hours).

Which would basically mean in the above example I want to take the overtime sum of the whole month and subtract 2 from it (2,5 - 2 = 0,5) and create a new field containing the output. If the overtime sum is below 2 it should be nulled.

Can anyone tell me how this can be achieved?

[kaydee64]

Does anyone have a solution to this problem?

[kaydee64]

Sorry for the late reply I have just now gotten around to trying it.

This Measure doesn't work for me. It doesn't let me use overtime inside the variable. I think it is because overtime is already a measured field. I can only select fields from the table but not Measures. Is there any way around this?

[DataInsights]

@kaydee64,

Try this measure:

Monthly Overtime = 
VAR vOvertime =
    SUM ( Hours[overtime] )
VAR vResult =
    IF ( vOvertime - 2 >= 2, vOvertime )
RETURN
    vResult