Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hey all,
I'm stuck with a calculation of number of FTEs. I have the amount of time employees should work, and the performed amount.
The employees are also categorized per departement and complany.
I can calculate the FTE for every employee, but i can't find out how to do a sum of these numbers, as the measure divides all the hours of all the employees combined.
I also tried using the query editor, but then i have different measures for week, month and year.
My goal is a table which you can expand/drill down up to employee level and week (of year) level, like this:
My data looks like this:
date | Company | departement | Name | Hours | HourstoWork |
18-Dec-20 | abc | sales | A | 0 | 8 |
17-Dec-20 | abc | sales | A | 8 | 8 |
16-Dec-20 | abc | sales | A | 8 | 8 |
15-Dec-20 | abc | sales | A | 8 | 8 |
14-Dec-20 | abc | sales | A | 8 | 8 |
11-Dec-20 | abc | sales | A | 8 | 8 |
10-Dec-20 | abc | sales | A | 8 | 8 |
09-Dec-20 | abc | sales | A | 8 | 8 |
08-Dec-20 | abc | sales | A | 8 | 8 |
07-Dec-20 | abc | sales | A | 0 | 8 |
18-Dec-20 | abc | internal | B | 8 | 8 |
17-Dec-20 | abc | internal | B | 8 | 8 |
16-Dec-20 | abc | internal | B | 8 | 8 |
15-Dec-20 | abc | internal | B | 8 | 8 |
14-Dec-20 | abc | internal | B | 8 | 8 |
11-Dec-20 | XYZ | operations | C | 8 | 8 |
10-Dec-20 | XYZ | operations | C | 8 | 8 |
09-Dec-20 | XYZ | operations | C | 8 | 8 |
08-Dec-20 | XYZ | operations | C | 8 | 8 |
07-Dec-20 | XYZ | operations | C | 8 | 8 |
Thanks in advance
Solved! Go to Solution.
Hi @Lars_c ,
Because you want a week level, you can create a calculated table as follows. There's a relationship between two tables.
Calendar =
ADDCOLUMNS (
CALENDAR ( MIN ( 'Table'[date] ), MAX ( 'Table'[date] ) ),
"Week",
YEAR ( [Date] ) & "-"
& WEEKNUM ( [Date] )
)
Create a measure.
Measure = DIVIDE(SUM('Table'[Hours]),SUM('Table'[HourstoWork]))
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Lars_c ,
Because you want a week level, you can create a calculated table as follows. There's a relationship between two tables.
Calendar =
ADDCOLUMNS (
CALENDAR ( MIN ( 'Table'[date] ), MAX ( 'Table'[date] ) ),
"Week",
YEAR ( [Date] ) & "-"
& WEEKNUM ( [Date] )
)
Create a measure.
Measure = DIVIDE(SUM('Table'[Hours]),SUM('Table'[HourstoWork]))
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
What if instead of Calender table we used Calculate Column like below in same table :
Year-Week = CONCATENATE(CONCATENATE((YEAR(Table[Date]),"-"),WEEKNUM(Table[Date]))
@Lars_c , what is formula you are trying, what is expcted output value
You can have a measure like
divide(sum(Table[Hours]), sum(Table[HourstoWork]))
use a date table.Date table can have month and year, that you can use in a matrix visual
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
86 | |
82 | |
64 | |
49 |
User | Count |
---|---|
124 | |
110 | |
88 | |
68 | |
66 |