cancel
Showing results for
Did you mean:
Frequent Visitor

## FTE calculation, with totals

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

1 ACCEPTED SOLUTION
Community Support

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 =
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.

3 REPLIES 3
Community Support

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 =
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.

Frequent Visitor

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]))

Super User

@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.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors