Forum Discussion
New field calculation based on Matrix Sum
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?
3 Replies
Try this measure:
Monthly Overtime = VAR vOvertime = SUM ( Hours[overtime] ) VAR vResult = IF ( vOvertime - 2 >= 2, vOvertime ) RETURN vResultSorry 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?
Does anyone have a solution to this problem?
