Help to sum value from another table but filtered by multiple columns that match both tables.

Hi Coltella8013 

you can write a measure as follows:

measure _WTE := 

var _region = selectedvalue ('first table' [region])

var _team = selectedvalue ('first table' [team])

var _jobtitle = selectedvalue ('first table' [job title])

return

calculate (sum( 'employee data' [ WTE avtual] ) , filter ('employee data' , 'employee data' [region] = _ region && 'employee data' [team] = _team  && 'employee data' [job title] = _jobtitle))

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly. 

Thank you! This is great. I have however noticed a flaw in my design and have had to change it slightly making it more complicated.

I have had to group the results of both tables using a union:

WTE Union = UNION(SELECTCOLUMNS(FILTER('Employee Data', 'Employee Data'[Employee Status] = "Active"), "Region", 'Employee Data'[Location], "Team", 'Employee Data'[Department], "Job Title", 'Employee Data'[Job Role]), SELECTCOLUMNS('Funded Establishment (WTE)', "Region", 'Funded Establishment (WTE)'[Region], "Team", 'Funded Establishment (WTE)'[Team], "Job Title", 'Funded Establishment (WTE)'[Job Role]))

 

This appears to be working.

 

I then introduced a lookup from the WTE fields in both original tables, however I have noticed where no result returns is is empty which I believe is causing charts not to render correctly.

 

I have tried an IF and IFEMPTY statement but cannot seem to get it right.

 

Any idea on the best way to handle the null / blank records in each table, i.e. replace empty with a 0 ?

 

 

 

Really appreaciate any ideas on this.

you can try 

+ 0

at the end of measure to deal with null.