SUMX Employee PTO Hours over Multiple Tables

[itsme]

I have a Calendar table, a table that lists holidays and # of PTO hrs on those dates, an employee table, a project assignment table that lists assignment id and employee (employee can have mulitple assignments), and a project forecast table for each assignment with dates and forecast hours. Here's an example of the model:

For May and July, PTO hours for each employee working on projects should look like this:

You see there are 7 Assignments, but only 6 employees - Robert has 2 different assignments. I need the table to look like this. When PTO hours are summed for July, there should be 48 hours (6 employees times 8 PTO hours). Unfortunately, my SUMX measure is showing 56 hours instead and 80 hours for May and July together when it should be 72 combined:

I am using: 

PTO_SUMX = SUMX ( 'Project Forecast', [PTO_SUM] )

where

PTO_SUM = SUM ( Holiday[Hours] )

If I use PTO_SUMX = SUMX ( DISTINCT ( 'Project Forecast'[Employee] ), [PTO_SUM] ) then I get the correct monthly values but incorrect sum for May and July together:

Can someone please help me? I feel like I'm so close to having this right!

Thank you so much in advance!

[tamerj1]

Hi @itsme 
Holiday is filtered only by Calendar table. I think you just need to iterate the Employee table as follows

PTO_SUMX =
SUMX ( VALUES ( 'Employee'[Employee] ), [PTO_SUM] )

[itsme]

This makes sense, but ends up giving me the results below, which is very close. The Month values are correct but the total should be 72, not 96.

Here's a breakdown of what your measure is doing on the Assignment Id level. ID's 1 to 4 should be a total of 8, not 16 because they only had 8 PTO hours for May. I need the grand total to sum the PTO for each consultant only once. Robert is on Assignment 1 and 2, and on a line level it should show 8 PTO hours for him on each Assignment, but should only count it once for the grand total, because we will be paying only for 8 hours of PTO even though he's working on 2 assignments.

[tamerj1 · Accepted solution]

@itsme 

You are right. I didn't know you are slicing by in your report.  Please try

PTO_SUMX =
SUMX (
    VALUES ( 'Calendar'[Month] ),
    CALCULATE ( SUMX ( VALUES ( 'Employee'[Employee] ), [PTO_SUM] ) )
)

[itsme]

@tamerj1 This seems to work!! Thank you!

[Dhacd]

Hi @itsme ,

Is can you try creating a relationship between the project assignment and the holiday table, The project assignment should slice the holiday table. Then this might show correctly.

If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.

Thanks and Regards,
Atma.

[itsme]

Unfortunately, we don't have the holiday table at an employee level. Since all employees on assignments recieve holiday pay, perhaps I need to make that table.