Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 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 correctlyHere we see that we predicted 2 out of 7 correctly

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

Capture.PNG

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

Capture.PNG

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.