Forum Discussion

ecarroll's avatar
ecarroll
Frequent Visitor
4 years ago
Solved

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!

 

Filter
sum
sumx
  • ecarroll's avatar
    ecarroll
    4 years ago

    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

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

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

  • ecarroll's avatar
    ecarroll
    Frequent Visitor
    4 years ago

    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
    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      4 years ago

      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

  • ecarroll's avatar
    ecarroll
    Frequent Visitor
    4 years ago

    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's avatar
      PaulDBrown
      Community Champion
      4 years ago

      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.