SumX when a column contains a word

[Anonymous]

Hello, 

I currently have this formula: 

Cleaning supplies = SUMX ( FILTER( CALCULATETABLE ('D1 - GL Data'),'D1 - GL Data'[Account]="CLEANING SUPPLIES"),'D1 - GL Data'[Actual])

This provides me the sum of the values in the GL Data Actual column when the value in the GL Data Account column is "CLEANING SUPPLIES". Works great! However, I want to change it to includes cleaning so it would sum the GL Data Actual column when just the word cleaning is in the GL Data account column. So it could be cleaning supplies, could be cleaning tools, etc. 

Thanks!

Mike

[Ashish_Mathur · Accepted solution]

Hi,

This measure works fine for me

=SUMX(FILTER(SUMMARIZE(VALUES(Data[Account]),Data[Account],"ABCD",SEARCH("Cleaning",[Account],1,-1),"EFGH",SUM(Data[Actual])),[ABCD]>0),[EFGH])

Hope this helps.