cancel
Showing results for
Did you mean:

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

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
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!
5 REPLIES 5
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!
Community Champion

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.

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

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.

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
Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!