SUMIFS equivalent

[ecarroll]

Hi - I have been trying to get a "SUMIFS" for lack of better term going across 2 tables. 1 table has employees, weeks they worked and billable hours. The other table has employees, weeks they worked, and nonbillable hours. I'm trying to pull the nonbillable into the billable. The billable is summarized by week and employee info but the nonbillable is broken out by different types of nonbillable work, so there are multiple lines per week. 

In the billable table I have tried many combinations of formulas (sum, sumx, allexcept, multiple filter formulas, if formulas, etc.), this is the latest that has not worked: 

Total Nonbill Hrs = calculate(sum(Nonbillable[Hours]),filter(Nonbillable,Nonbillable[Employee Name]=[Employee Name] && Nonbillable[week]=[week]))

This is failing to give me the total for that specific week, instead it is just giving me the total nonbillable hours worked for that person every single week, which obviously very much skews my data. 

Any help would be greatly appreciated, I'm new at PowerBI/DAX but advanced at excel, so I didn't think this would be that difficult to accomplish... clearly I was wrong. 

Thank you!

[ecarroll · Accepted solution]

Hi, thanks for helping but I don't think it is quite working, as I don't think this person worked 2,325 billable hours the week of 1/10/22. I also was looking to calculate the total nonbillable hours and add that up to the billable to get to total hours. 

However I think i figured out that my formula just needed to refer to the specific table it was in, because it seems to be working fine now that I have added the total hrs reference: 

Total Nonbill Hrs = calculate(sum(PM_NonbillableHoursOngoing[Hours]),filter(PM_NonbillableHoursOngoing,PM_NonbillableHoursOngoing[Employee Name]=TOT_HrsByEE[Employee Name] && PM_NonbillableHoursOngoing[week]=TOT_HrsByEE[Week]))

Thanks!

[PaulDBrown]

Sorry, my mistake. When I copied your data for the PBIX file, I didn't notice the decimal character was a "."  My locale sees commas as the decimal character. So basically 23.25 became 2325 in my file., when I should have corrected it.
The structure of the model I posted is the recommended best practice. It makes life much easier.

[ecarroll]

Sure! The power bi file has a bit too much going on so I can only provide the tables.

Here is the nonbllable data - as you can see there are multiple entries for each week, sometimes there could be multiple for each day, broken out by a "service code". I need this info for something else so I can't use a summarized version of it.

WIP Date	Service	Hours	Employee Name	Week
Wednesday, January 5, 2022	ADM02	5	dlwahos icran,	1/3/2022
Monday, January 10, 2022	ADM02	9	dlwahos icran,	1/10/2022
Monday, January 17, 2022	ADM02	12	dlwahos icran,	1/17/2022
Monday, January 24, 2022	ADM02	12	dlwahos icran,	1/24/2022
Monday, January 31, 2022	ADM02	8	dlwahos icran,	1/31/2022
Monday, February 7, 2022	ADM02	13	dlwahos icran,	2/7/2022
Wednesday, February 16, 2022	ADM02	6	dlwahos icran,	2/14/2022
Monday, February 21, 2022	ADM02	12	dlwahos icran,	2/21/2022
Monday, February 28, 2022	ADM02	11	dlwahos icran,	2/28/2022
Monday, January 10, 2022	ADM01	0.75	dlwahos icran,	2/21/2022
Monday, January 17, 2022	ADM01	0.75	dlwahos icran,	1/10/2022
Monday, January 24, 2022	ADM01	0.75	dlwahos icran,	1/17/2022
Monday, January 31, 2022	ADM01	0.75	dlwahos icran,	1/24/2022
Monday, February 7, 2022	ADM01	0.75	dlwahos icran,	1/31/2022
Monday, February 14, 2022	ADM01	0.75	dlwahos icran,	2/7/2022
Monday, February 21, 2022	ADM01	0.75	dlwahos icran,	2/14/2022
Monday, February 28, 2022	ADM01	0.75	dlwahos icran,	2/21/2022
Thursday, January 6, 2022	ADM01	0.5	dlwahos icran,	2/28/2022
Thursday, January 6, 2022	ADM01	0.5	dlwahos icran,	1/3/2022
Thursday, January 13, 2022	ADM26	4	dlwahos icran,	2/21/2022
Wednesday, January 26, 2022	ADM26	1.5	dlwahos icran,	1/10/2022
Monday, February 21, 2022	ADM26	3	dlwahos icran,	1/24/2022
Monday, February 28, 2022	ADM26	3	dlwahos icran,	2/21/2022
Monday, January 3, 2022	ADM03	8	dlwahos icran,	1/3/2022
Tuesday, January 4, 2022	ADM26	8	dlwahos icran,	1/3/2022
Monday, January 24, 2022	ADM01	5	dlwahos icran,	2/14/2022
Monday, February 7, 2022	ADM01	2	dlwahos icran,	1/24/2022
Wednesday, January 19, 2022	ADM09	0.75	dlwahos icran,	2/7/2022
Thursday, February 3, 2022	ADM18	1.75	dlwahos icran,	1/17/2022
Friday, February 4, 2022	ADM18	2	dlwahos icran,	1/31/2022
Thursday, January 6, 2022	ADM19	8	dlwahos icran,	1/31/2022
Thursday, January 20, 2022	ADM19	1.25	dlwahos icran,	1/3/2022
Thursday, February 3, 2022	ADM08	3	dlwahos icran,	2/21/2022
Monday, January 10, 2022	ADM08	0.5	dlwahos icran,	2/28/2022
Thursday, January 6, 2022	ADM09	0.5	dlwahos icran,	1/17/2022
Tuesday, January 18, 2022	ADM09	0.5	dlwahos icran,	1/3/2022
Monday, January 10, 2022	ADM01	1	dlwahos icran,	2/7/2022
Monday, January 17, 2022	ADM01	1	dlwahos icran,	1/10/2022
Monday, January 31, 2022	ADM01	1	dlwahos icran,	1/17/2022
Wednesday, February 16, 2022	ADM18	1	dlwahos icran,	1/31/2022
Monday, January 10, 2022	ADM02	1.5	dlwahos icran,	2/28/2022

Here is the total hours table, which already has billable hours by week, and I want to factor in nonbillable for each of those weeks, then add them up to get a total hours. I included a "SUMIFS" column which shows what the #'s should be. The formula for that (in excel) is: SUMIFS(Nonbillable[Hours],Nonbillable[Employee Name],[@[Employee Name]],Nonbillable[Week],[@Week])

Employee Name	Total Billable Hrs	Week	Total Nonbill Hrs	SUMIFS	Total Hrs
dlwahos icran,	26.25	2/7/2022	153.25	15.5	179.5
dlwahos icran,	25	1/31/2022	153.25	19.75	178.25
dlwahos icran,	22.75	1/24/2022	153.25	17.75	176
dlwahos icran,	28	2/28/2022	153.25	13.5	181.25
dlwahos icran,	27.75	2/21/2022	153.25	23.5	181
dlwahos icran,	35.5	2/14/2022	153.25	11.75	188.75
dlwahos icran,	11	1/3/2022	153.25	23.25	164.25
dlwahos icran,	25	1/17/2022	153.25	16	178.25
dlwahos icran,	23.25	1/10/2022	153.25	12.25	176.5

[PaulDBrown]

Thank you for the data. Let's see if this is what you are after...

Fristly you need to set up the model. 

You Need to create dimension tables for fields common to both tables. These dimension tables must contain unique values for the fields and cover the range of data for the field in the model. For this exercise, I've created dimension tables for Employee and Week. You then create one-to-many relationships between these dimension tables and each corresponding field in the tables.

 These relationships enable filtering from the "one" side table to the "many" side tables. So if you create visuals using fields from these dimension tables, these will filter the corresponding rows in the Total Hours  table and the Non-billable table.

You can then create simple SUM measures for the Total Billable and the non-billable hours. Create a visual using the employee name and week fields from the dimension tables and add the measures to get:

I've attached the sample PBIX file

[PaulDBrown]

Can you provide a sample PBIX file with non-confidential data, or sample data of both tables?