Countif a column cell matches another column cell

[Anonymous]

Hello!

We want to see how well we predicted boat purchases compared to actual purchases.

As long as the "Actual Boat Purchased" Column contains what is in the "Boat we predicted a customer would buy" column, it should count as a match (it should not be an exact match, just a contains).

In the example below, we would have a success prediction rate of 2 out of 7 (28%) because the "Actual Boat Purchased" column contains "Bassmaster 8" 2 times (which is what we purchased). 

How could I create a measure that tells us the "Success Prediction Rate"

Example file

Here we see that we predicted 2 out of 7 correctly

[v-piga-msft · Accepted solution]

Hi @Anonymous ,

For your requirement, we could create a calculated column and a measure to achieve that.

tag =
IF (
    FIND (
        'Sales'[Boat we predicted a customer would buy],
        'Sales'[Actual Boat Purchased],
        1,
        0
    ),
    1,
    0
)
measure =
VAR a =
    CALCULATE (
        COUNT ( 'Sales'[tag] ),
        FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[tag] = 1 )
    )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Sales' ),
        ALLSELECTED ( 'Sales'[Boat we predicted a customer would buy] )
    )
RETURN
    DIVIDE ( a, b )

Here is the output.

Best  Regards,

Cherry