Hello everyone.
I have computed a personal rate for a company each day (the number of workers working each day * number of hours per contract) and taking into account if they are on holidays (0) or in a sick leave (0).
Date rate=
var num= SUMX(VALUES('Staff'[TRABAJADOR]),CALCULATE(SUMX('Staff','Staff'[% hours worked]),FILTER('Staff','Staff'[Start date]<=MAX('Calendar'[Date]))))
RETURN num* CALCULATE([Sick multiplier],USERELATIONSHIP(Staff[ID worker],'IT/ABS'[employee]))*[day multiplier]
If I put it in a column with the name of the worker, it won't add anything to the grand total, therefore I did:
Total rate =
var summarizedTable = ADDCOLUMNS (
SUMMARIZE ( 'Staff', 'Staff'[Worker],'Staff'[% hours worked]),
"DaysW", CALCULATE ([Date rate]) )
RETURN SUMX(summarizedTable,[Date rate])
And it seems to work nice. Here is an example of the matrix I get (values are Total rate)
| Worker | Day 1 | Day 2 | Day 3 | Day 4 |
AAAA | 0,5 | 0,5 | 0,5 | 0 |
BBBB | 1,00 | 1,00 | 1,00 | 1,00 |
| CCCC | 0,75 | 0,75 | 0,75 | 0,75 |
| DDDD | 0,875 | 0,875 | 1,00 | 1,00 |
| EEEEE | 0,00 | 1,00 | 0,00 | 0,00 |
| Total | 3,125 | 4,125 | 3,25 | 2,75 |
What I need to achieve now, is the average of the total for all days. If I try to do an averagex over total rate, it doesn't work.
Any help would be very appreciated.
