Forum Discussion
Solved
Equivalent of Excel SUMIFS between two tables
Hi, New to PowerBI so a steep learning curve, but how to i acheive the following. (i didnt design tables, legacy from previous team) Table 1, contains the Job#, Line# and the Type of job that...
rmorris For SUMIF you can use SUMX(FILTER(),...) or CALCULATE like:
Type C Time Column = VAR __Job = [Job#] VAR __Lines = SELEECTCOLUMNS(FILTER('Table1',[Job#] = __Job && [Type] = "C"),"__Line",[Line#]) RETURN SUMX(FILTER('Table2',[Job#] = __Job && [Line#] IN __Lines),[Time Spent])
Greg_Deckler
Community Champion
Community Championrmorris For SUMIF you can use SUMX(FILTER(),...) or CALCULATE like:
Type C Time Column =
VAR __Job = [Job#]
VAR __Lines = SELEECTCOLUMNS(FILTER('Table1',[Job#] = __Job && [Type] = "C"),"__Line",[Line#])
RETURN
SUMX(FILTER('Table2',[Job#] = __Job && [Line#] IN __Lines),[Time Spent])
Thank you so much, worked a treat!