Forum Discussion
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 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
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])
2 Replies
- Greg_Deckler
Community Champion
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])Thank you so much, worked a treat!
