Forum Discussion
FTE (Fulltime Equivalent) Calculation with conditions
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]
2 Replies
jgeddesSuper User
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
_fteCalcSorry 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
