Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 Try:
Column =
VAR __User = [User]
VAR __Week = [Week Number]
VAR __Table = FILTER('Table',[User] = __User && [Week Number] = __Week)
RETURN
SUMX(__Table,[Hours])
Wow, worked perfectly! Thanks very much for your accurate and fast response.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |