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

# Calculating Win and Loss %

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 %

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.