Forum Discussion
Overtime calculation
I am trying to calculate weekly over time of total Duration.
The formula(s) that I am currently using are:
TotalHours = SUM(HoursData[Duration])
Overtime = IF([TotalHours] > 37.5, IF(([TotalHours]-37.5)>6.5, 6.5, ([TotalHours]-37.5)), 0)
While using the Overtime DAX Formula, it does calculate properly on the row level but when it comes to Matrix Total, its always showing 6.5
Any way to fix this please?
Thanks
17 Replies
- MarkLaf
Super User
I think something like this should work.
Overtime= SUMX( HoursData, MAX( MIN( SUM( HoursData[Duration] ) - 37.5, 6.5 ), 0 ) )Alternatively, this is the kind of thing that you could tackle with a calculated column.
Hi MarkLaf - The proposed solution did not work properly. It gave different numbers than to what I am expecting. But thank you
- MarkLaf
Oops. I shouldn't have had SUM in there. I think this should do it. If not, it would be helpful to provide some test data and more info on your model.
Overtime= SUMX( HoursData, MAX( MIN( HoursData[Duration] - 37.5, 6.5 ), 0 ) )
PadycosmosSolution Sage
Hope this helps:
Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5,0)
- Padycosmos
Hope this edited one helps:
Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5<=0,0)
- Padycosmos
Please create a calculated column instead of Measure
- Padycosmos
If the data is not sensitive, could you please share the pbix file?
- Padycosmos
Hope this helps:
Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5,0)
- Padycosmos
Hope this helps:
Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5<=0,0)
Hi Padycosmos - It says there is a syntax error
- Padycosmos
Hope this helps:
Overtime =SWITCH(TRUE(),[TotalHours]-37.5>6.5, 6.5,[TotalHours]-37.5)<=6.5, [TotalHours]-37.5,0)
