Measure with contains and starts with

[abaak]

Hi all,

I need a measure with a calculation of Revenue with some filters but i am not sure how to do this. I know this is possible in the visual filters, but I need to add another calculation to this one after it that is why I need it in a measure.

I need the sum from Revenue

Which contains or starts with "BV" from Order

And starts with "PA" from Note

I was trying something with CALCULATE(SUM( )) and FILTER and FIND() but obviously not getting the results I want.

Below the sample data, at the end I only need the €61, €23 and €65 below.

Revenue	Order	Note
€       123,00	BV45	GF23
€          61,00	BV46	PA143
€          48,00	OR456	CD32
€          65,00	OR457	PA32
€          45,00	OR458	PA124
€          23,00	BV47	PA543
€          65,00	BV48	PA234
€          56,00	BV48	CD42
€          43,00	ORD459	RG43
€          23,00	OR460	RG34
€          43,00	BV46	CD52

Thanks in advance!

[iamprajot · Accepted solution]

Here it is,

CALCULATE(AVERAGE(TableName[Revenue]),FILTER(TableName,LEFT(TableName[Order],2)="BV" && LEFT(TableName[Note],2)="PA"))

[abaak]

Thanks alot! I was actually pretty close! 

[iamprajot]

Sure.
The solution provided earlier was if BV and PA are at starting position and here is another one if they fall between the text.
CALCULATE(AVERAGE(TableName[Revenue]),FILTER(TableName,FIND("BV",TableName[Column 1],1,0) > 0 && FIND("PA",TableName[Column 1],1,0) > 0))
check the brackets if i missed any at closing and hit some kudos if this worked and helped.
Thanks

[Greg_Deckler]

Sample data please.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[abaak]

Done! hopefully this makes it more clear.