Forum Discussion
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 | January | February | Total |
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 |
| Project | January | February | Total |
| Project A | 0.35 | 0.55 | 0.45 |
| Project B | 0.7 | 0.65 | 0.675 |
| Project C | 0.95 | 0.8 | 0.875 |
| Total | 2 | 2 | 2 |
Any ideas?
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.
Hi jsangerman ,
Try to show values as "Percent of column total". Or create a measure like below.
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.
9 Replies
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.
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/21 1/2/21 1/3/21 1/4/21 1/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/21 1/2/21 1/3/21 1/4/21 1/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.
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.
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.
