Win Rate Based on Multiple Conditions

[Anonymous]

Hi everyone,

I am looking to create a win rate percentage based off multiple conditions. 

I have two data sets and want to compare the product, customer, and quantity divided by the number of times the product was quoted at the specific quantity per each distinct customer. For example if milk cartons were quoted at 5 quantity for 10 customers, but only 4 of the customers bought 5 milk cartons the win rate would be 40%.

Basically the product, customer, and quantity must be matching for both the numerator and denominator. 

I created a "Won" column that compares all of these yet I'm still unsure how to get it working properly.

I hope this makes sense and any help would be immensely appreciated.

[aj1973]

Hi @Anonymous 

Can you share a sample of your model?

[Anonymous]

@aj1973 

I'm new so I hope this is what you mean/helps.

The sales file looks something like this.

Customer Name                  Product               Quantity

Bob                                      Milk                     1

Billy                                      Milk                     1

Kate                                     Milk                     1

Whereas the Quote File looks something like this

Customer Name                  Product               Quantity

Bob                                      Milk                     1

Billy                                      Milk                     1

Kate                                     Milk                     1

Ted                                      Milk                     1

Tom                                     Milk                     1

Chris                                    Milk                     500

Simon                                  Milk                     5

Jim                                       Milk                     1

What I'm looking for is to match the customer, product, and quantity with each other divided by the number of times the product(milk) was quoted that also matches. 

There are 6 quotes that match and 3 of them were actually purchased. Therefore, with filters, the win rate would be 50%. 

[AlexisOlson]

I can't quite tell for sure how you expect your example to generalize with multiple Products but my best guess is that you want something like this:

Test =
VAR SalesCombinations =
    DISTINCT (
        SELECTCOLUMNS (
            Sales,
            "Product_Quantity", Sales[Product] & "_" & Sales[Quantity]
        )
    )
RETURN
    DIVIDE (
        COUNTROWS ( Sales ),
        COUNTROWS (
            FILTER (
                Quotes,
                Quotes[Product] & "_" & Quotes[Quantity] IN SalesCombinations
            )
        )
    )

[Anonymous]

Thank you for the suggestion. It did not work unfortunately. Basically, I have slicers that differentiate between each product. The formula you used looks like this for me:

Win Rate = 
VAR SalesCombinations = 
  DISTINCT(
     SELECTCOLUMNS(
     'QuoteSalesMerge',"Quantity",'QuoteSalesMerge'[PARKER_PART] & 'QuoteSalesMerge'[QUANTITY]
                  )
          )
Return 
     DIVIDE(
           COUNTROWS('QuoteSalesMerge'),
           COUNTROWS(
             FILTER(
              QuoteSalesMerge,
                QuoteSalesMerge[Sales History Cur FY.PART] & QuoteSalesMerge[Sales History Cur FY.QTY] IN SalesCombinations
                   )
                   )
           )

 The "_" caused an error for me so I removed it. 

One formula I had before was: 

Win Rate = DIVIDE(CALCULATE(COUNT('QuoteSalesMerge'[Match]),'QuoteSalesMerge'[Match] = "Won"),'Sales History Cur FY'[QuoteCount])

Where 'QuoteSalesMerge'[Match]' is a column that returns "Won" if the product, part, and customer on the sales sheet match the quote sheet. I realize that it is always dividing 2 by the number of quotes there are because of the count function, but I am unsure how to track the "Won" and divide it by the quote count total.

[AlexisOlson]

Well, I certainly wouldn't expect it to work in an entirely different situation than it was written for. In particular, it definitely isn't going to work as written with the numerator and denominator counting rows from the same table (I expected two separate tables similar to the ones you mentioned with Sales a subset of Quotes). I cannot solve a separate problem that you have not described.

[Anonymous]

You're right. I had a many-to-many relationship issue which is why I merged the two tables for the product, quantity, and customer. I was never able to reference one another due to that issue.

[v-janeyg-msft]

Hi, @Anonymous 

Has your problem been solved? Do you still need help? If you still need help, can you share a many-to-many version of the sample file? 

Best Regards

Janey Guo