FTE (Fulltime Equivalent) Calculation with conditions

[ashnoti]

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]

[ashnoti]

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

[jgeddes]

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