Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Gurus
I apologise in advance if I have made any beginner errors in my question below as I have never posted a question.
I have built a headcount (FTE) report based on daily hours table that contains an employee number, Employee status (Permanent/Casual/Agency), Hours for each hour type (Normal, Overtime etc.), Date worked. I also have a Date table that assigns dates to a month based on a 4-4-5 basis and financial year (July to June). I have a [Report Days] Measure that calculates the number of working days in the user selected date range.
Sample data and Pbix: pbix file sample data
The FTE calculation is based on the employee's status:
1. Perm it is simply a distinct count of the employee number (Disregards the hours worked).
2. For any other status it is total hours divided by the number of standard hours in a month (e.g. October is a 4 week month and standard hours is 152hrs, if a casual works 100Hrs the head count would be 100/152 = 0.66 of a head (Fulltime Equivalent).
Perm = IF Employee Status = "Perm", Distininct count of employee number within the date range selected.
ELSE DIVIDE(Total worked hours, Standard hours in date range,0)
Problem: In some months an employee has hours in Perm as well as Casual which means they are counted as 1 under Perm as well as under Casual based on the hours worked (Overstated FTE). The rule I wish to apply is as follows:
Over the user selected date range,
IF an employee has hours under Perm AND any other Labour Group,
Then for each Employee FTE = DIVIDE(Total Hours,(Report Days]*7.6) with no distinction of Labour Group.
ELSE IF employee has hours in Perm Only
Then FTE = 1,
ELSE FTE = DIVIDE(Total Hours,(Report Days]*7.6).
Currently I see the same employee under Perm and Casual:
I want the report to show 27/152 = 0.18 FTE under Perm and 61/152=0.40 under Casual.
Relationships: Dates table and Hours Report Table - [Date Key]-[Date]
Sorry for such a late reply.
Is there a way we can ensure that the totals sum the individual employee FTE?
The total Perm FTE is showing 2.11 but it should show 2.05 for Employee 440009
You can try something like...
Employee FTE Calculation =
var _allEmployeeHours =
CALCULATE(
[Total Hrs], //calculate total hours
ALLSELECTED(Dates1[Date Key]), //use all of the dates selected by the slicer
ALLEXCEPT(Hours_Report, Hours_Report[Employee Number]) // remove the visual context filters except the employee number
)
var _fteCalc =
SWITCH(
TRUE(),
CALCULATE([Total Hrs], Hours_Report[Labour Group] = "Perm") = _allEmployeeHours, 1, // if the sum of total hours in the 'Perm' group equals all the hours for that employee FTE = 1
DIVIDE([Total Hrs], [Work Days] * 7.6, 0)
)
Return
_fteCalc
Proud to be a Super User! | |
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |