Using a function Like or Search to calculate

[Anonymous]

Hi guys,

I have the following table:

Name	Value
Car One	100
Car Two	150
Bike One	10
Bike Two	15
Bike Three	20

What I want to do is to calculate all the values that correspond to a field that has the word "bike"in it and divide them by all: For example "Calculate(SUM(Table[Value]),  Table[Name]="Bike")  this calculation gives me the  values that

only use the word "Bike". As a result i would like to have (10+15+20)/(10+15+20+100+150).

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may create the measure with SEARCH Function.

Measure =
DIVIDE (
    SUMX (
        FILTER ( Table3, SEARCH ( "Bike", Table3[Name], 1, 0 ) > 0 ),
        Table3[Value]
    ),
    SUM ( Table3[Value] )
)

Regards,

Cherie

[PattemManohar]

@Anonymous Please try this as a New Measure

Test171 = 
VAR _BikeSum = SUMX(FILTER(ALL(Test171PatternMatchSum),LEFT(Test171PatternMatchSum[Name],4)="Bike"),Test171PatternMatchSum[Value])
VAR _Total = SUM(Test171PatternMatchSum[Value])
RETURN FORMAT(DIVIDE(_BikeSum,_Total),"#0.00")

[Anonymous]

@PattemManohar

If you write "Car"instead the result will be 1 because it takes all the data that has n+ characters. So it will only work if the name is the longest.

[PattemManohar]

@Anonymous The initial question that you have posted is to know the Total Sales that contains Bike in the text. 

Please post the appropriate test data and expected output to suggest an accurate solution.