I’ve been going round in circles with an issue and I’m sure it’s to do with SUMX but I don’t know how to resolve it.
I’m trying to create a report that tracks employee’s flexitime balance by week.
I’ve got one measure where the columns don’t add up to the sum of the individual weeks.
I’ve got the following tables
Time = Employee, date, hours booked, task
Calendar = Date, Week No., Year
Employee = Employee, Working hours per week
I’ve got the following measures that seem to work ok and subtotal ok:
- Hours Booked (this is just the sum of hours submitted)
- Flex Hours Taken (this is a calculation of Hours booked where the Task = Flexitime)
- Hours Booked less Flex (Hours booked - Flex Hours Taken)
- Hours per week (Just the number of contracted hours per week)
- No of weeks (distinct count of week number)
- Contracted hours - (Hours per week * No of weeks)
So my issue is with calculating the flex hours gained in a week:
Flex Hours Gained = if((Hours Booked Less Flex - Contracted Hours)<0,0, Hours Booked less Flex-Contracted hours)
This is the measure that has a mismatch between the individual weeks and the column total.
Having read around I believe this can be resolved with SUMX but I’m not sure of the syntax in this particular case.
Can anyone point me in the right direction?