Forum Discussion

Spyker's avatar
Spyker
New Member
3 years ago
Solved

SUMIFS equivalent for calculated column

Hi,

I am attempting to recreate a simple SUMIFS formula in PowerBI. The attached picture is an example of the table I want, with the column to be calculated in red and the SUMIFS formula used for that column shown. Basically, I want to know how many hours a specific user worked in a specific week, and I need that figure to be in on that data entry (row).

 

 

I have had a good look online and none of the solutions seems to work with me. The following seems to get close, but I assume the issue is not being able to differentiate between a column and a cell as I can in excel with the sumifs formula.

Total hours = CALCULATE(SUM('Table'[Hours]),'Table'[User]='Table'[User], 'Table'[Week Number]= 'Table'[Week Number])

 

The actual data set to be used it fairly large and I eventually want to use the column in a gantt chart so I believe I need to use a calculated column for this. Is this even possible? Please let me know if I havn't explained anything very well.

 

Thanks in advance.

Need Help
Show and Tell
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    Spyker Try:

    Column = 
  VAR __User = [User]
  VAR __Week = [Week Number]
  VAR __Table = FILTER('Table',[User] = __User && [Week Number] = __Week)
RETURN
  SUMX(__Table,[Hours])

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Spyker Try:

    Column = 
  VAR __User = [User]
  VAR __Week = [Week Number]
  VAR __Table = FILTER('Table',[User] = __User && [Week Number] = __Week)
RETURN
  SUMX(__Table,[Hours])

     

    • Spyker's avatar
      Spyker
      New Member
      3 years ago

      Wow, worked perfectly! Thanks very much for your accurate and fast response.