Reply
lc1 Frequent Visitor
Frequent Visitor
Posts: 13
Registered: ‎09-19-2018

Help to create percentage formula using sum of various values divided by unique value

Good morning,

I need to calculate % Absenteeism by department. The data I have available is as follow:

Capture.PNG

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.

 

Thanks,

LC

AlB Super Contributor
Super Contributor
Posts: 1,186
Registered: ‎11-12-2018

Re: Help to create percentage formula using sum of various values divided by unique value

Hi @lc1

 

Is the "Leave Hours" value always the same for a  Period/employee? If so:

DIVIDE(SUM(Table[PAR_HOURS]);

             VALUES(Table[Leave Hours]))

lc1 Frequent Visitor
Frequent Visitor
Posts: 13
Registered: ‎09-19-2018

Re: Help to create percentage formula using sum of various values divided by unique value

No, it is usually 80 hours biweekly per pay period, but it can change (like the example above)

lc1 Frequent Visitor
Frequent Visitor
Posts: 13
Registered: ‎09-19-2018

Re: Help to create percentage formula using sum of various values divided by unique value

Also, I get an error when I add the rest of the employees to the calculation.

AlB Super Contributor
Super Contributor
Posts: 1,186
Registered: ‎11-12-2018

Re: Help to create percentage formula using sum of various values divided by unique value

In the example above it does not change, it's 72 for all of them

lc1 Frequent Visitor
Frequent Visitor
Posts: 13
Registered: ‎09-19-2018

Re: Help to create percentage formula using sum of various values divided by unique value

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.

AlB Super Contributor
Super Contributor
Posts: 1,186
Registered: ‎11-12-2018

Re: Help to create percentage formula using sum of various values divided by unique value

[ Edited ]

@lc1

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

 VALUES(Table[Leave Hours]))

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

 

lc1 Frequent Visitor
Frequent Visitor
Posts: 13
Registered: ‎09-19-2018

Re: Help to create percentage formula using sum of various values divided by unique value

When I try to combined all employees from the same division this is what I get ( huge percentage)

 

Capture 1.PNG

AlB Super Contributor
Super Contributor
Posts: 1,186
Registered: ‎11-12-2018

Re: Help to create percentage formula using sum of various values divided by unique value

[ Edited ]

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] ) )
)

 

lc1 Frequent Visitor
Frequent Visitor
Posts: 13
Registered: ‎09-19-2018

Re: Help to create percentage formula using sum of various values divided by unique value

[ Edited ]

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 1Employee 2Employee 3Total Department
 PAR_HoursLeave HoursPAR_HoursLeave HoursPAR_HoursLeave HoursPAR_HoursLeave Hours
 356880880  
 2456880    
 156880    
 856      
 856      
 456      
Total4856248088080216
%Abs(48/56)86%(24/80)30%(8/80)10%(80/21637%