FTE Calculation with Totals

[PhilBrookbanks]

Hi, I'm hoping somebody can help with the below issue. I've recreated an example in excel as I think this is the easiest way to show.

My model has 2 tables. A sales table and a staff HR table containing the number of hours worked and FTE (hours/40).

Sales Table

User ID	Date Sold
Madelaine Hall	14/03/2022
Lydia Slade	29/03/2022
Amy Humphris	20/03/2022
Jo Dews	12/03/2022
Robyn Archer	25/03/2022
Amanda Hamilton	25/03/2022
Lydia Slade	05/03/2022
Amy Humphris	26/03/2022
Jo Dews	06/03/2022
Madelaine Hall	25/03/2022
Lydia Slade	04/03/2022
Amy Humphris	11/03/2022
Jo Dews	26/03/2022
Robyn Archer	29/03/2022
Amy Humphris	28/03/2022
Jo Dews	30/03/2022
Robyn Archer	05/03/2022
Amanda Hamilton	12/03/2022
Lydia Slade	13/03/2022

Staff Table

User ID	Hours	FTE
Madelaine Hall	40	1
Lydia Slade	40	1
Amy Humphris	35	0.875
Jo Dews	20	0.5
Robyn Archer	40	1
Amanda Hamilton	30	0.75

I have a relationship on the staff ID between the 2.

I wish to produce a table in Power BI that shows total sales by staff member alongside hours worked and FTE like the below.

User ID	Sales	Hours	FTE
Madelaine Hall	2	40	1
Lydia Slade	4	40	1
Amy Humphris	4	35	0.875
Jo Dews	4	20	0.5
Robyn Archer	3	40	1
Amanda Hamilton	2	30	0.75
Total	19	205	5.125

I'm struggling with the DAX to get the totals for Hours and FTE without them adding up for every sale. e.g. 2 sales and 40 hours becoming 80 hours. Any help would be greatly appreciated.

Thanks

Phil

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Tahreem24]

@PhilBrookbanks After my below post I am attaching PBIX for your reference.

[Tahreem24 · Accepted solution]

@PhilBrookbanks I replicated your data at my end and simply joining one to many cardinality based on UserID, I just dragged and dropped the fields from both the table and it's showing me the correct result.

[PhilBrookbanks]

Worked when I switched the table to use the User ID dimension from the staff table rather than the orders table.

[amitchandak]

@PhilBrookbanks , Measures like

sold = sum(sales[sales])

Total Hours = summarize(Staff , Staff [User ID], Staff[Hour]), [HOUR])

Total FTE= summarize(Staff , Staff [User ID], Staff[FTE]), [FTE])

[PhilBrookbanks]

Hi,

Thank you for your reply, I'm not sure that this solution works as my staff table is already a dimension table with unique entries I already essentially have it summarised by staff id and hours. my issue is that when looking at data from the fact table (sales) it is summing up the assocated hours and therefore the total in the table becomes a sumproduct. I need the measure to not sum in the table and then the total to be the sum of these.