Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jsangerman
Helper I
Helper I

Calculating full-time equivalent (FTE)

I'm having trouble coming up with the formula to calculate a full-time equivalent based on employee timesheet data. A full-time equivalent refers to the time spent on a given project as a fraction (decimal) of time spent on all projects.

 

Here's what the data looks like:

 

Table - Effort Data

Person Name

Project Name

Date

Effort (hours)

 

Here are some facts to help understand the data:

  • The total for a Person will always be 1
  • The total for a Date will always equal the total number of people
  • The total for a Project will always be the sum of the full-time equivalent measure for all people
  • The time for the projects used in the denominator of the FTE calculation (the "all" projects) respects any filters on Project Name

It's the last two parts that are throwing me off in the formula.

 

Here's what the data might look like:

 

Person

  Project

JanuaryFebruaryTotal

Mike Wallace

  Project A

  Project B

  Project C

1

  0.25

  0.5

  0.25

1

  0.25

  0.25

  0.5

1

  0.25

  0.375

  0.375

Dan Rather

  Project A

  Project B

  Project C

1

  0.1

  0.2

  0.7

1

  0.3

  0.4

  0.3

1

  0.2

  0.3

  0.5

 

ProjectJanuaryFebruaryTotal
Project A0.350.550.45
Project B0.70.650.675
Project C0.950.80.875
Total222

 

Any ideas?

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

Your simple exceptions aren't that simple. In the first case you can use the MIN() function, for example 

 

hourstocount = MIN(8,hoursworked)

 

to cap at 8 (ignoring the fact that you are penalizing the worker for doing more work).  For the second scenario use ALL() or ALLSELECTED() or ALLEXCEPT() to modify the filter context inside the computation.

View solution in original post

Hi @jsangerman ,

 

Try to show values as "Percent of column total". Or create a measure like below.

effort.jpg

 

Measure =
DIVIDE (
    SUM ( 'Table'[Efforts (hours)] ),
    CALCULATE (
        SUM ( 'Table'[Efforts (hours)] ),
        ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[User] )
    )
)

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

 

View solution in original post

9 REPLIES 9
lbendlin
Super User
Super User

Your simple exceptions aren't that simple. In the first case you can use the MIN() function, for example 

 

hourstocount = MIN(8,hoursworked)

 

to cap at 8 (ignoring the fact that you are penalizing the worker for doing more work).  For the second scenario use ALL() or ALLSELECTED() or ALLEXCEPT() to modify the filter context inside the computation.

ALL() is the one that got me closest! Thank you for the suggestion.

 

Thank you to everyone else for the suggestions. It all helped me come to something that works.

lbendlin
Super User
Super User

Which business question do you need to answer? How many FTEs worked on each project each day/month?

 

Since each of your employees seems to be a "pure" FTE  (working all regular working hours on one of the projects) there's no math to be had for daily employee stats.

Yes, we're trying to understand how many FTEs are working on each project for a given time period.

 

There is still math to be done; each person could be working on multiple projects for any time period, from day to month, quarter, and year.

lbendlin
Super User
Super User

Not clear where your "effort in hours"  comes in.  Also, are projects always running for full months?

Users enter time they spend on each project each day. So, the raw data looks like this:

 

User

  Project

1/1/211/2/211/3/211/4/211/5/21

Mike Wallace

  Project A

  Project B

  Project C

 

3 hr

4 hr

1 hr

 

1 hr

0 hr

7 hr

 

3 hr

0 hr

5 hr

 

0 hr

2 hr

6 hr

 

3 hr

3 hr

2 hr

 

The FTE could calculate this like this

User

  Project

1/1/211/2/211/3/211/4/211/5/21

Mike Wallace

  Project A

  Project B

  Project C

 

0.375 hr

0.5 hr

0.125 hr

 

0.125 hr

0 hr

0.875 hr

 

0.375 hr

0 hr

0.625 hr

 

0 hr

0.25 hr

0.75 hr

 

0.375 hr

0.375 hr

0.25 hr

 

Projects run for full months, but the monthly data is really a summary shown in a visualization.

Hi @jsangerman ,

 

Try to show values as "Percent of column total". Or create a measure like below.

effort.jpg

 

Measure =
DIVIDE (
    SUM ( 'Table'[Efforts (hours)] ),
    CALCULATE (
        SUM ( 'Table'[Efforts (hours)] ),
        ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[User] )
    )
)

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

 

Hi,

There should be a simple measure to sum up the hours and divide by 8.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Seems like it should be simple. Our team needs to know the data a little differently: 1) the data should be normalized to 8 daily hours, such that a person who works 12 hours in a day still counts for 1 FTE for that day; 2) we want to be able to exclude some projects using filters, such that a person who works 4 hours on Project A and 4 hours on Project B will count as 1 FTE when we filter Project B out of a visualization.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.