Help need with a measure and totals

[huguestremblay]

Hello,

I have a report where I calculate work productivity based on the number of hours worked by each employees of a group compared to their individual targets.  The date at which the employee joined the group should be taken into account when doing the overall calculations but I cannot get it to work.  I tried creating a Measure that include the Start Date (from the PS table) but without success.

I have created a simplified version of my situation here as I cannot share my original report. The 2 tables are linked by the Name columns.

The table shows the target number of hours for each employee, and has blank values for employees that did not work in a given month (e.g. Eddie joined in April and Chloe in May).  It works on the individual rows, but the Total includes all employees, even for the month where they were not in the group yet.

What I would need is the total to be 407.4 for March, 575.4 for April and 743.4 for May...

I later use that value in another calculation along with the actual number of hours worked to get the productivity.  Right now, everything works as expected if I select an individual employee, but as soon as I have more than one, or the entire team, the productivity is underestimated because it uses the target total as if all employees had been in the group forever.

My simplified report is available here.

Any help in getting this to work would be realloy appreciated!

[tamerj1 · Accepted solution]

Hi @huguestremblay 
Please use the following measure

Target Hours = 
CALCULATE ( 
    SUM ( PS[Target Hrs/Mo] ),
    CROSSFILTER ( PS[Name], Entry[Name], BOTH )
)

[HotChilli]

I suppose a simple solution would be to merge the Target Hrs column into the Entry table in power query. 

-

I know it's a simplified model but the problem is that with PS table filtering Entry the relationship, the dates can't retrieve accurate data from the PS table (it's a bit of an anomaly that the hours targets -apart from the total - are correct in the matrix because there is only one hours value for each name)

--

Alternatives are, I think, generating Target HRs for each month in the PS table and using a Name dimension table to filter PS and Entry.  Haven't tested this but you can see that the merge in my first suggestion effectively does this in a simpler way.

[huguestremblay]

Thank you for the suggestions, I will give that a try.