Forum Discussion
SUMIFS equivalent
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!
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 |
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
