Ask Search:
Kathleen VanHooserKathleen VanHooser 

Creating a Win Loss Percentage Formula in a Report using Stage

Hi

I have been tasked with creating a new report for sales that calculates their win rate on a report that shows:

Total Created Opportunities
Won
Lost
Win Loss%

We dont use standard stages here.  We use Closed Lost = 0% and Onboard Complete = 100%

Any help would be appreciated!

Thanks in advance.
Kathleen
Best Answer chosen by Moderator (salesforce.com) 
Kemi LeKemi Le
-
When you edit your formula, you should be able to pick where the formula will be displayed.  that's where it is going to summarize the numbers. Do you have them set to Grouping 1?  You can pick All Summary Levels and you will see the totals at the bottom as well as in the groups.

User-added image

All Answers

Kemi LeKemi Le
Hi Kathleen,

You could try something like this:

Create a summary opportunity report and add formulas using the WON standard field.

Total Won (number format)  = WON:MAX
Total Lost ( number format) = RowCount-WON:MAX
Win Loss % (percent format)  = WON:SUM/RowCount
To get total, just summarize one of the columns, but if you are hiding details and you want to show this in line with your other formulas you can do a number formula and summarize RowCount.  

Just make sure to create your formulas in the order you want to see them on the report, from left to right.

You would have something like this:


Report





Steve MolisSteve Molis
Here's a report and some CSF's the I built to calculate Close Rate and Win Rate ->
User-added image
2
User-added image
3
User-added image





Kathleen VanHooserKathleen VanHooser
Hi Kemi,

Thank you so much for the response!  I appreciate it.

I don't have standard won fields that I can see. 

I do know 100% = complete
and 0% = loss

How would I write this as a formula in the report using the same scenario below.

Here is a screen shot of the report

User-added image
Kemi LeKemi Le
Hey there, I see it in your field chooser on the left - it is the 12th one down I think - it shows # Won.  Right before Close Date.  the # isn't part of the field name - it's there to show what type of field it is.
Kathleen VanHooserKathleen VanHooser
Kemi,

Thank you so much!  I am getting closer.  I created the 3 formula fields that you advised.  The only problem is the numbersare not totaling at the bottom.
 
I am not sure how to summarize them?

Also does that Win Loss Rate Look Correct?User-added image

Here is a screen shot:
Kathleen VanHooserKathleen VanHooser
The numbers for total won and lost are not correct when I drill down to the detail.

What am I doing wrong?????
Kemi LeKemi Le

Ahh, sorry I had errors in my formula.  Use these.

Total Won (number format)  = WON:SUM
Total Lost ( number format) = RowCount-WON:SUM
Win Loss % (percent format)  = WON:SUM/RowCount

For your summary level grouping make sure you have it set for all summary levels.  This will summarize at the bottom as well.
Kathleen VanHooserKathleen VanHooser
You are a doll!  That worked.  Thanks so much!

One more thing though....I dont understand what you mean by summary  level grouping?

Where do I put those?  I know how to summarize a field, but am unable to on these custom formula fields.

I appreciate your help very much!
Kathleen
Kemi LeKemi Le
-
When you edit your formula, you should be able to pick where the formula will be displayed.  that's where it is going to summarize the numbers. Do you have them set to Grouping 1?  You can pick All Summary Levels and you will see the totals at the bottom as well as in the groups.

User-added image
This was selected as the best answer
Kathleen VanHooserKathleen VanHooser
I saw that....after I typed you!

You are wonderful!!
Kemi LeKemi Le
Glad I could help!
Steve MolisSteve Molis
Kathy,
Show a sister some love and mark Kemi's solution as "Best Answer"
Kathleen VanHooserKathleen VanHooser
Done!

Thanks, I am new to using "answers."
Steve MolisSteve Molis
Kathy, you still haven't marked Kemi's solution as "Best Answer"
Kathleen VanHooserKathleen VanHooser
Steve,

I thought I did.

I am new to using the community. I clicked on most helpful...what else am I supposed to do?

Thanks
Steve MolisSteve Molis
If someone posts the solution to your question then you should click "Mark as Best Answer"
Kristine DenzonKristine Denzon
Definitely the Wonderful Best Answer! May I ask how I can be able to show the Win/Loss % in the Dashboard? I have been trying but the option to select win/loss% is not available. I am not really sure if this is possible. Thank you so much!!!!
Andreas SlovacekAndreas Slovacek
These answers are all excellent!  Can anyone link me to documentation on the "WON," "CLOSED," "RowCount" fields?
Thank you in advance!  
Malik ZeeMalik Zee
Hello,

Thanks for this thread, I'm trying to create a Win/Loss report and am inching closer.

Question - my "Won/Closed" fields display as checkboxes instead of an actual value, how can I change this and does this make a difference when trying to create the Win/Loss report?