Win Rate Based on Multiple Conditions

Question

Hi everyone,&nbsp;I am looking to create a win rate percentage based off multiple conditions.&nbsp;&nbsp;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%.&nbsp;Basically the product, customer, and quantity must be matching for both the numerator and denominator.&nbsp;&nbsp;I created a "Won" column that compares all of these yet I'm still unsure how to get it working properly.&nbsp;I hope this makes sense and any help would be immensely appreciated.&nbsp;

aj1973 · Answer

Hi&nbsp;Anonymous&nbsp;Can you share a sample of your model?

anonymous · Answer

aj1973&nbsp;I'm new so I hope this is what you mean/helps.The sales file looks something like this.&nbsp;Customer Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Product&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QuantityBob&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Milk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Billy&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Milk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Kate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Milk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;&nbsp;Whereas the Quote File looks something like thisCustomer Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Product&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QuantityBob&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Milk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Billy&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Milk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Kate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Milk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Ted&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Milk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Tom&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Milk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Chris&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Milk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;500Simon&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Milk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5Jim&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Milk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;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.&nbsp;There are 6 quotes that match and 3 of them were actually purchased. Therefore, with filters, the win rate would be 50%.&nbsp;

alexisolson · Answer

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:&nbsp;Test =
VAR SalesCombinations =
    DISTINCT (
        SELECTCOLUMNS (
            Sales,
            "Product_Quantity", Sales[Product] &amp; "_" &amp; Sales[Quantity]
        )
    )
RETURN
    DIVIDE (
        COUNTROWS ( Sales ),
        COUNTROWS (
            FILTER (
                Quotes,
                Quotes[Product] &amp; "_" &amp; Quotes[Quantity] IN SalesCombinations
            )
        )
    )

anonymous · Answer

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] &amp; 'QuoteSalesMerge'[QUANTITY]
                  )
          )
Return 
     DIVIDE(
           COUNTROWS('QuoteSalesMerge'),
           COUNTROWS(
             FILTER(
              QuoteSalesMerge,
                QuoteSalesMerge[Sales History Cur FY.PART] &amp; QuoteSalesMerge[Sales History Cur FY.QTY] IN SalesCombinations
                   )
                   )
           )&nbsp;The "_" caused an error for me so I removed it.&nbsp;&nbsp;One formula I had before was:&nbsp;Win Rate = DIVIDE(CALCULATE(COUNT('QuoteSalesMerge'[Match]),'QuoteSalesMerge'[Match] = "Won"),'Sales History Cur FY'[QuoteCount])&nbsp;Where&nbsp;'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 · Answer

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.

Forum Discussion

Win Rate Based on Multiple Conditions

7 Replies

Related content

Multiple conditions Index

Conditional formatting based on multiple conditions

Multiple Donut charts with conditions

Look up data based on multiple conditions

Conditional Column with Multiple Conditions