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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Lars_c
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:Schermafbeelding 2021-01-28 172641.png

 

My data looks like this:

dateCompanydepartementNameHoursHourstoWork
18-Dec-20abcsalesA08
17-Dec-20abcsalesA88
16-Dec-20abcsalesA88
15-Dec-20abcsalesA88
14-Dec-20abcsalesA88
11-Dec-20abcsalesA88
10-Dec-20abcsalesA88
09-Dec-20abcsalesA88
08-Dec-20abcsalesA88
07-Dec-20abcsalesA08
18-Dec-20abcinternalB88
17-Dec-20abcinternalB88
16-Dec-20abcinternalB88
15-Dec-20abcinternalB88
14-Dec-20abcinternalB88
11-Dec-20XYZoperationsC88
10-Dec-20XYZoperationsC88
09-Dec-20XYZoperationsC88
08-Dec-20XYZoperationsC88
07-Dec-20XYZoperationsC88

 

Thanks in advance

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
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 =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[date] ), MAX ( 'Table'[date] ) ),
    "Week",
        YEAR ( [Date] ) & "-"
            & WEEKNUM ( [Date] )
)

11.png

12.png

 

Create a measure.

Measure = DIVIDE(SUM('Table'[Hours]),SUM('Table'[HourstoWork]))

 

13.png

 

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.

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
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 =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[date] ), MAX ( 'Table'[date] ) ),
    "Week",
        YEAR ( [Date] ) & "-"
            & WEEKNUM ( [Date] )
)

11.png

12.png

 

Create a measure.

Measure = DIVIDE(SUM('Table'[Hours]),SUM('Table'[HourstoWork]))

 

13.png

 

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

 

 

amitchandak
Super User
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.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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