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

[Coltella8013]

Hi.

I am hoping someone can assist here, I am trying to find a way to sum the column WTE Actual from an Employees table but to group by / filter by three other columns that exist. I've included an example image and file attached.

At present there are no relationships between the two tables.

Example PBX can be found here: https://we.tl/t-JaXeT8Nm7N

Any tips would be much appreciated.

[Selva-Salimi · Accepted solution]

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. 

[Coltella8013 · Accepted solution]

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.

[elitesmitpatel]

Hi @Coltella8013 
Here is my solution 

i have made realtionship and its working fine have  look at 1 at the image.

and in 2nd point of the image  i have applied the formula mentioned above by @Selva-Salimi  it worked but their was a problem in getting the total in the bottom , so i prefered to make the realtionship between the table .

here is the file -->
Help-to-sum-value-from-another-table-but-filtered-by-multiple 

If it help please appreciate the work and accept it as solution

[Selva-Salimi · Accepted solution]

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. 

[Coltella8013 · Accepted solution]

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.

[elitesmitpatel · Accepted solution]

you can try 

+ 0

at the end of measure to deal with null.

[Coltella8013]

This appears to show 0.00 in the empty values but the measure does not sum show the totals and all show 0.00 regardless of the figures displayed above. A bit like this issue: Solved: Totals showing as 0 instead of the sum - Microsoft Fabric Community

I have however managed to find a working solution by using a relationship as per your previous suggestion, but using 2 inactive (many-many) relationships with the Region and Team and an active (many-many) relationship with the job title in each original table. This is now behaving exactly as required.

Thanks all for your help.