Forum Discussion
Using a function Like or Search to calculate
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).
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
4 Replies
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")
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.
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.
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
