Equivalent of Excel SUMIFS between two tables

[rmorris]

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 it is.

Job#		Line#	Type
C4662		1	C
C4662		2	W
C4662		3	C
C4662		4	I
C4662		5	C
C5000		1	C

Table 2, contains the Job# and Line# but  details the time spent on each line.

Job#		Line#	Time Spent
C4662		1	1.5
C4662		2	3.5
C4662		3	6.2
C4662		4	0.7
C4662		5	1.2
C5000		1	10.2

I have a third table that is a summary table where i need to add three columns that will show the sum of time spent by each line type. This is what the end results needs to look like. Do i add these as new columns, or a measure, and what function do i use to achieve it?

Job#		Type C Time	Type I Time	Type W Time
C4662		8.9	0.7	3.5
C5000		10.2	0.0	0.0

Job# is the only current relationship between tables

Thank you

[Greg_Deckler · Accepted solution]

@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])

[rmorris]

Thank you so much, worked a treat!