Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ecarroll
Frequent Visitor

SUMIFS equivalent

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!

 

1 ACCEPTED SOLUTION
ecarroll
Frequent Visitor

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!

View solution in original post

5 REPLIES 5
ecarroll
Frequent Visitor

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!

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






ecarroll
Frequent Visitor

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 DateServiceHoursEmployee NameWeek
Wednesday, January 5, 2022ADM025dlwahos icran,1/3/2022
Monday, January 10, 2022ADM029dlwahos icran,1/10/2022
Monday, January 17, 2022ADM0212dlwahos icran,1/17/2022
Monday, January 24, 2022ADM0212dlwahos icran,1/24/2022
Monday, January 31, 2022ADM028dlwahos icran,1/31/2022
Monday, February 7, 2022ADM0213dlwahos icran,2/7/2022
Wednesday, February 16, 2022ADM026dlwahos icran,2/14/2022
Monday, February 21, 2022ADM0212dlwahos icran,2/21/2022
Monday, February 28, 2022ADM0211dlwahos icran,2/28/2022
Monday, January 10, 2022ADM010.75dlwahos icran,2/21/2022
Monday, January 17, 2022ADM010.75dlwahos icran,1/10/2022
Monday, January 24, 2022ADM010.75dlwahos icran,1/17/2022
Monday, January 31, 2022ADM010.75dlwahos icran,1/24/2022
Monday, February 7, 2022ADM010.75dlwahos icran,1/31/2022
Monday, February 14, 2022ADM010.75dlwahos icran,2/7/2022
Monday, February 21, 2022ADM010.75dlwahos icran,2/14/2022
Monday, February 28, 2022ADM010.75dlwahos icran,2/21/2022
Thursday, January 6, 2022ADM010.5dlwahos icran,2/28/2022
Thursday, January 6, 2022ADM010.5dlwahos icran,1/3/2022
Thursday, January 13, 2022ADM264dlwahos icran,2/21/2022
Wednesday, January 26, 2022ADM261.5dlwahos icran,1/10/2022
Monday, February 21, 2022ADM263dlwahos icran,1/24/2022
Monday, February 28, 2022ADM263dlwahos icran,2/21/2022
Monday, January 3, 2022ADM038dlwahos icran,1/3/2022
Tuesday, January 4, 2022ADM268dlwahos icran,1/3/2022
Monday, January 24, 2022ADM015dlwahos icran,2/14/2022
Monday, February 7, 2022ADM012dlwahos icran,1/24/2022
Wednesday, January 19, 2022ADM090.75dlwahos icran,2/7/2022
Thursday, February 3, 2022ADM181.75dlwahos icran,1/17/2022
Friday, February 4, 2022ADM182dlwahos icran,1/31/2022
Thursday, January 6, 2022ADM198dlwahos icran,1/31/2022
Thursday, January 20, 2022ADM191.25dlwahos icran,1/3/2022
Thursday, February 3, 2022ADM083dlwahos icran,2/21/2022
Monday, January 10, 2022ADM080.5dlwahos icran,2/28/2022
Thursday, January 6, 2022ADM090.5dlwahos icran,1/17/2022
Tuesday, January 18, 2022ADM090.5dlwahos icran,1/3/2022
Monday, January 10, 2022ADM011dlwahos icran,2/7/2022
Monday, January 17, 2022ADM011dlwahos icran,1/10/2022
Monday, January 31, 2022ADM011dlwahos icran,1/17/2022
Wednesday, February 16, 2022ADM181dlwahos icran,1/31/2022
Monday, January 10, 2022ADM021.5dlwahos 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 NameTotal Billable HrsWeekTotal Nonbill HrsSUMIFSTotal Hrs
dlwahos icran,26.252/7/2022153.2515.5179.5
dlwahos icran,251/31/2022153.2519.75178.25
dlwahos icran,22.751/24/2022153.2517.75176
dlwahos icran,282/28/2022153.2513.5181.25
dlwahos icran,27.752/21/2022153.2523.5181
dlwahos icran,35.52/14/2022153.2511.75188.75
dlwahos icran,111/3/2022153.2523.25164.25
dlwahos icran,251/17/2022153.2516178.25
dlwahos icran,23.251/10/2022153.2512.25176.5

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.
model.jpg

 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:

Result.jpg

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PaulDBrown
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.