Sum two columns if third column contains text

[CesiCash]

H there,

I want to do a simple calculation which seems to be pretty easy in Excel but i dont get the solutions in PBI.

I have column A which has names in it.

The value in Column B and C should be added in Column D, if the text column A contains "ing" or "fy". Otherwise only the value in column B shoud be taken.

Thats an example how my data an column D should look like

A               B      C    D

testing       1     1    2

testfy         1     1    2

test            1     3    1

testfying    1    2     3

Thanks

Carina

[v-danhe-msft]

Hi @CesiCash,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @CesiCash,

Based on my test, you could refer to below steps:

Sample data:

Create the calculated column:

Column = var a=IF(IFERROR(SEARCH("ing",[A]),BLANK())<>BLANK(),TRUE(),FALSE())||IF(IFERROR(SEARCH("fy",[A]),BLANK())<>BLANK(),TRUE(),FALSE())
        return IF(a=TRUE(),[B]+[C],[B]) 

Result:

You could also download the pbix file to have a view.

https://www.dropbox.com/s/v95btioek957och/Sum%20two%20columns%20if%20third%20column%20contains%20tex...

 Regards,

Daniel He

[abbasabdulla]

Hi,

Try this function in Caluclated Column 

if Text.Contains([A], "ing") or  Text.Contains([A], "fy") then [B]+[C] else [B]

[CesiCash]

Hi @abbasabdulla

Unfortunately there is no text.contains function in powerbi?

Is there any other idea?

Best

Carina

[abbasabdulla]

Hi, I don't know which powerbi products do you test my function in, I have tested it using Power BI Desktop Version: 2.41.4581.361 64-bit (November, 2016) Check the below Pibx file I worked on. https://drive.google.com/open?id=12RIcR-QVFB1waMO2w56n4OD9iq8byHcD