- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-04-2018 07:08 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-04-2018 07:37 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-04-2018 07:56 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-04-2018 08:01 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-04-2018 08:02 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

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

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-04-2018 08:46 AM

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

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

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

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

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

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% |