Countif a column cell matches another column cell

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 wo...

Need Help

v-piga-msft

7 years ago

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

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 )

Forum Discussion

Countif a column cell matches another column cell