Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Win Rate Based on Multiple Conditions

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...
Need Help
aj1973's avatar
aj1973
Icon for Community Champion rankCommunity Champion
5 years ago

Hi Anonymous 

Can you share a sample of your model?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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.