1 Reply Latest reply on Aug 21, 2018 2:27 PM by Wesley Magee

    Calculating Win and Loss %

    Megha Bhatnagar

      Hi,

      My data set has 4 fields - opp id, Opp Owner, Incumbents, Winners

      I am trying to calculate the Win, Loss and Pending % per Owner.

      For example if Owner 1 has 10 opportunities and he wins 5 his win % would be 50, Similarly for loss and pending

      The condition to create win, loss and pending are as follows:-

      If Incumbents <> AMZ, AMZ Inc, Amz Inc for Internal and Winner = AMZ, AMZ Inc, Amz Inc for Internal then Win (I have to count these numbers)

      If Incumbents <> AMZ, AMZ Inc, Amz Inc for internal and Winner <> AMZ, AMZ Inc, Amz Inc for Internal then Loss (count these numbers)

      If Incumbents is NULL and Winner is NULL then Pending (count)

       

      Then % will be number of wins per owner/number of opportunities per owner

      % Loss will be number of losses per owner/number of opportunities per owner

      Pending number of pending opps per owner/number of opportunities per owner

       

      I am attaching a mock workbook

        • 1. Re: Calculating Win and Loss %
          Wesley Magee

          Megha,

          To create what you're looking for I made 6 calculations (3 for the counts and 3 for the %s). Each of the counts returns the Opp ID if it meets the qualifications. If you want to see the pure counts of wins, losses, pending; just wrap them in a COUNTD.

           

          They are as follows:

           

          Win

          If (Incumbents <> "AMZ" AND Incumbents <> "AMZ Inc" AND Incumbents <> "Amz Inc" AND Incumbents <> "Amz Inc for Internal")

          AND ([Winners1] = "AMZ" OR  [Winners1] = "AMZ Inc" OR [Winners1] = "Amz Inc" OR [Winners1] = "Amz Inc for Internal")

          THEN [Opp id]

          END

           

          Loss

          If (Incumbents <> "AMZ" AND Incumbents <> "AMZ Inc" AND Incumbents <> "Amz Inc" AND Incumbents <> "Amz Inc for Internal")

          AND ([Winners1] <> "AMZ" AND  [Winners1] <> "AMZ Inc" AND [Winners1] <> "Amz Inc" AND [Winners1] <> "Amz Inc for Internal")

          THEN [Opp id]

          END

           

          Pending

          IF ISNULL([Incumbents]) and ISNULL([Winners1])

          THEN [Opp id]

          END

           

          Win %

          COUNTD([Win]) / COUNTD([Opp id])

           

          Loss %

          COUNTD([Loss]) / COUNTD([Opp id])

           

          Pending %

          COUNTD([Pending]) / COUNTD([Opp id])

           

          Please let me know if you have any questions.

          -Wesley

           

          If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.