## Desktop

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:

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

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

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)

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.

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

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.

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

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)

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

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 1 Employee 2 Employee 3 Total Department PAR_Hours Leave Hours PAR_Hours Leave Hours PAR_Hours Leave Hours PAR_Hours Leave Hours 3 56 8 80 8 80 24 56 8 80 1 56 8 80 8 56 8 56 4 56 Total 48 56 24 80 8 80 80 216 %Abs (48/56) 86% (24/80) 30% (8/80) 10% (80/216 37%