Calculated column filtered by a column that contains specific text

[LEK_RGMI]

I have a table of objects with a Type and Rank. Some objects have more than one Type. I want to make a simple calculation that finds "Culture" anywhere in the Type column and then averages the Ranks. It's ok if it has more than 1 Type, like "Culture, Growth"; an object like that will be included twice--in the average for "Culture" and "Growth" separately. I'll want to do this calculation for every Type, but don't know if it can be in one measure or if I will have to create a measure for each Type.

My expression is calculating the average Rank for the objects that ONLY contain "Culture". But what I want it to do is find the number that is at the bottom of the table--the average for all objects containing "Culture" ANYWHERE in the Type column, even if it has other Types. The equivalent SQL expression would say something like, Type LIKE "%Culture%", but I can't find anything in DAX that works. I have issues when I try to use CONTAINS or CONTAINSSTRING even though others online have allegedly used it for essentially the exact same purpose as mine. I feel like I'm missing something simple... Please help!

[JirkaZ · Accepted solution]

Something like this should work:

AVGX([YourTable], IF(CONTAINSSTRING([Type], "Culture"), Rank))