SUMIFS equivalent for calculated column

[Spyker]

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.

[Greg_Deckler · Accepted solution]

@Spyker Try:

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

[Spyker]

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