'sumif' equivalent for power bi

[Anonymous]

Hi all - sorry if this is a simple one, or posted and answered before! 

New user to power bi - trying to figure out how to do a sumif equivalent formula

I have two tables, one is the raw data and the second is the summary data (example below)

I need a summary of the raw recovered field based on the individual 'job', calculated to the 'tot_recov' fields

raw						summary
job	from	to	recovered			job	depth	tot_recov
h1	0	10	9.8			h1	24.8
h1	10	14.8	4.7			h2	12
h1	14.8	16.9	2.1			h3	16.2
h1	16.9	24.8	7.8
h2	3	6.5	3.5
h2	6.5	9	2.4
h2	9	12	2.8
h3	0	9	8.8
h3	9	10.8	1.6
h3	10.8	15	4.2
h3	15	16.2	1.1

in excel, I would use a simple sumif equation in the tot_recov field, like; =SUMIF(A:A,G3,D:D)

so I figure in power bi something like; = CALCULATE(SUM('raw'[recovered]),'raw'[job]='summary'[job])

however, this fails due to "expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."

I figure this is meaning it would like a single criteria (eg. 'raw'[job]="h1" or along these lines), but this is illogical for this data set as there is approx. 500 individual jobs 

data is from 2 different data sources but linked through the 'job' field

Thanks in advance! 

[harshnathani]

Hi @Anonymous ,

You can use the below Measure.

Measure1 = CALCULATE(SUM('raw'[recovered]), ALLEXCEPT( 'raw', raw[job])

else create a TABLE

Summary TABLE = ADDCOLUMNS ( 
                                                          SUMMARIZE ( 'raw', raw[job]),

                                                           "depth" , CALCULATE(SUM('raw'[recovered]), ALLEXCEPT( 'raw', raw[job]))

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[camargos88]

Hi @Anonymous ,

Once it's related you don't need to write this. Only :

SUM(raw[recovered])

[amitchandak]

@Anonymous , You can use Sumx

Sumx(Table,<expression>)

Here you can use filter in Table and You can use if in expression

https://docs.microsoft.com/en-us/dax/sumx-function-dax

[Anonymous]

worked perfectly! thanks @amitchandak