Forum Discussion
FTE Calculation with Totals
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
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.
6 Replies
Tahreem24Super User
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.
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])
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.
- Tahreem24
PhilBrookbanks After my below post I am attaching PBIX for your reference.
- Ashish_Mathur
