cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abaak Frequent Visitor
Frequent Visitor

Measure with contains and starts with

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.

 

RevenueOrderNote
 €       123,00BV45GF23
 €          61,00BV46PA143
 €          48,00OR456CD32
 €          65,00OR457PA32
 €          45,00OR458PA124
 €          23,00BV47PA543
 €          65,00BV48PA234
 €          56,00BV48CD42
 €          43,00ORD459RG43
 €          23,00OR460RG34
 €          43,00BV46CD52

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
iamprajot Established Member
Established Member

Re: Measure with contains and starts with

Here it is,

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

5 REPLIES 5
Super User
Super User

Re: Measure with contains and starts with

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


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

Proud to be a Datanaut!


abaak Frequent Visitor
Frequent Visitor

Re: Measure with contains and starts with

Done! hopefully this makes it more clear.

iamprajot Established Member
Established Member

Re: Measure with contains and starts with

Here it is,

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

Highlighted
abaak Frequent Visitor
Frequent Visitor

Re: Measure with contains and starts with

Thanks alot! I was actually pretty close! 

iamprajot Established Member
Established Member

Re: Measure with contains and starts with

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