12-04-2018 07:08 AM
I need to calculate % Absenteeism by department. The data I have available is as follow:
The formula I'm trying to use is sum of PAR-HOURS (24) divided by 72 (instead of 72*3). And have the formula work for all employees during one pay period selected.
12-04-2018 07:37 AM
Is the "Leave Hours" value always the same for a Period/employee? If so:
12-04-2018 08:08 AM
That's only one employee of many. Total employees are approximately 4000 and each take time off.
For example out of 80 hours paid in a period, 16 could've been sick time which means 16/80=20% Absenteeism for that specific employee.
The challenge for me is add each employees PAR-Hours and divide them by each employee Leave Time. And get a total % Absenteeism by Department.
12-04-2018 08:24 AM - edited 12-04-2018 08:25 AM
Ok, so if you set up a matrix with rows employee ID and period then you have the details of one employee per row and thus
will provide you with a scalar. That assumes this value is the same for the selected employee in the selected period, as the 7 shown in your table
12-04-2018 09:00 AM - edited 12-04-2018 09:01 AM
How do you define the measure for all employees, as the average of all of them? If so, try this:
AVERAGEX ( VALUES ( Table[EmplID] ), CALCULATE ( SUM ( Table[PAR_HOURS] ) / VALUES ( Table[LeaveHours] ) ) )
12-04-2018 10:16 AM - edited 12-04-2018 10:43 AM
The measure should be:
Sum of PAR_Hrs per employee divided by Leave Hours (note that I only need 1 value of Leave Hours not the sum of them)
|Employee 1||Employee 2||Employee 3||Total Department|
|PAR_Hours||Leave Hours||PAR_Hours||Leave Hours||PAR_Hours||Leave Hours||PAR_Hours||Leave Hours|