how to exlude text from formula

[Chrisjr]

Hello All,

I have the following table:

Name	Value
CocaLight	5
CocaZero	6
Zero	3
Extra	1
PesMax	2

I want to calculate the sum of the values but exlude all the name containing "Coca" in its name. 

What would be the formula ?

Thanks

[Chrisjr]

thanks @Barthel 

[Barthel · Accepted solution]

Hey @Chrisjr,

You can use the CONTAINSTRING function in combination with a CALCULATE for this. SUM the 'Value' column, where the 'Name' does not contain the value 'Coca':

Formula = 
CALCULATE ( 
    SUM ( 'Table'[Value] ),
    KEEPFILTERS ( NOT CONTAINSSTRING ( 'Table'[Name], "Coca" ) )
)

If you need 'Coca' to be case sensitive, use the CONTAINTSSTRINGEXACT function instead of CONTAINSSTRING.