Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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]))
No, it is usually 80 hours biweekly per pay period, but it can change (like the example above)
Also, I get an error when I add the rest of the employees to the calculation.
In the example above it does not change, it's 72 for all of them
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.
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
Hi @lc1,
I made one sample for your reference. If it doesn't meet your requirement, kindly share your pbix and excepted result to me. You can upload the files to onedrive and share the link here.
Create the measure as below.
Measure = CALCULATE(SUM('Table'[PAR_HOURS]),ALL('Table'),VALUES('Table'[EMPLID]))/SUM('Table'[Leave Hours])
For more details, please check the pbix as attached.
Regards,
Frank
Please let me know if you can access this link.
Hi @lc1,
I cannot access the link as you shared. Could you please share the file again by another way?
Regards,
Frank
I have an excel file but don't know how to share it other than by mail or screen shot.
I'm looking for monthly % of absenteeism for Occ Code 8050 for division 71. Based on past reporting methods results should be around 20%. (Using only Unsheduled Absenteeism, already noted in the Report level filters)
% Absenteeism = AVERAGEX(VALUES(Absenteeism[EMPLID]),CALCULATE(SUM(Absenteeism[PAR_HOURS])/MIN(Absenteeism[Leave Hours])))
How can I adjust this formula to select the "MIN(Table[Leave Hours])" per pay period? The Leave Hours value will be the same per pay period but could change every month.
When I try to combined all employees from the same division this is what I get ( huge percentage)
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] ) ) )
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% |
It's not adding them, VALUES() gives you just one value
I'm using your formula and I'm getting this error message:
It's the VALUES(...) then
Assuming the value Table[Leave Hours] is the same for a period/employee you're showing, you can use MIN, or MAX, or AVERAGE It's hard fro me to do any more without the data model
AVERAGEX ( VALUES ( Table[EmplID] ), CALCULATE ( SUM ( Table[PAR_HOURS] ) / MIN ( Table[Leave Hours] ) ) )
User | Count |
---|---|
93 | |
86 | |
68 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |