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
Community 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
    Super 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.
      • AlexisOlson's avatar
        AlexisOlson
        Super User
        5 years ago

        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.