Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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?
Solved! Go to Solution.
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.
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.
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.
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.
Hi,
There should be a simple measure to sum up the hours and divide by 8.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |