How do I build a win/loss ratio report? - Answers - Salesforce Trailblazer Community
Ask Search:
Greg StevensGreg Stevens 

How do I build a win/loss ratio report?

I'm trying to build a report that shows each sales reps win/loss ratio expressed as a percentage.   I can't find literature that instructs me how to do this.  Any help would be greatly appreciated.
Ruth MargolisRuth Margolis
After giving this a cursory try on my own, I've come to the following conclusion.  To do this you need a separate field for Won and Lost on your opportunity page.

If the opportunity is Closed Won, the Won field will populate (with a date, a 1 or a Yes.)  If the opportunity is Closed Lost, then it too will populate with a similar indicator.

THEN: Build a matrix report, with AEs on the rows and Won and Lost on the columns.  A bit of a formula at the end of the row:  et Voila!

I am SURE there's a better way to do this, but I know my janky methods work.
Steve MolisSteve Molis
You might be able to do this using a Custom Report Summary Formula.  Can you create a mock-up of what you're looking for in Excel or GoogleDocs and post a screenshot or URL Link? 

Something like this: 
User-added image
Ruth MargolisRuth Margolis
I'm fooling with a Custom Report Summary Formula, the problem is getting it to recognize that Status has different counts for different results. 
Ruth MargolisRuth Margolis

Got it!

Okay, Do a Summary Report, Group by AE, then by Status.

Do a custom formula, inserted at the AE level. 

The formula is SUM Closed/Sum Won.

Et Voila!

I am so smart.  SMRT!

Steve MolisSteve Molis
These are the formulas I used in that Report
User-added image

User-added image
Steve MolisSteve Molis
Damn you! @Screamapillar I woulda beat you if I hadn't posted screenshots! ;-p 
Ruth MargolisRuth Margolis
And @SteveMo trumps me again.
Javier GonzalezJavier Gonzalez
This is one of those answers that we should be able to "sticky". It comes up a lot and you guys have answered it beautifully. 
Greg StevensGreg Stevens
Thank you.  I'm not able to find the field that lets me summarize the count of opportunities as shown in stevemo's screenshots (helpful, worth the wait!)
Steve MolisSteve Molis
Hi Greg, all of the field that I refernced ar standard SFDC Opportunity Fields.  Which one can you not find?
Greg StevensGreg Stevens
-
In this screen shot you have a collumn totaling the # of opportunities.  My wife always says I don't look hard enough, but I can't find that in my standard field options and I swear I've looked hard.   

User-added image
Steve MolisSteve Molis
That's just a custom Formula(Number,0) field that I added on my SFDC Org that displays the number 1 on every record, it comes in handy when you want to display the COUNT of ...
But you do not need that for either of my Formulas, in this example it's completlely redundant with the standard Record Count value. 
Greg StevensGreg Stevens
Incredibly helpful.  Definately going to roll that out.  Surprising that's not a built in field in more areas, i can see how we're going to use that on a large number of things.  Count is always a good thing to have.  Thank you guys.
Jo GoldenJo Golden
Can you do this to included "open-awarded" stage ? Not just closed (we keep "awarded" stage as a "win" but do not "close" it until the project is over).... thanks for all the help -- these posts are all very helpful!
Gaylene DickensGaylene Dickens
Can this be modified to get a ratio of the Grand Summary rather than the row count?  I need to dashboard the ratio of leads in a current status against the total recieved.  We are already using a snapshot report to pull historical data -vs- current stage, but the state values are not summary fields that I can run a report formula on ..
Steve MolisSteve Molis
Hi Dickens3,
I think that should be doable, can you post a screenshot of your CSF settings and the report you're trying to build?
Gaylene DickensGaylene Dickens
- Here is the report with bucket fields .. I'd like to have the YES bucket / by the row count to get a ratio, I have only been able to get a formula to work if I put in a hard number to divid by, this won't work on a month to month since the value will change.  For Feb it would be 5.5 (1978/360).
Ratio Report

Lam Aik PeaLam Aik Pea
I want to add 3 columns into my report: sum of all open opportunities, sum of won opportunities, and sum of lost opportunities. I also want to add a formula field to calculate sum of won opportunities/sum of all opportunities as the last column.

My report will look something like this:

                                      Sum of open opps    Sum of won opps     Sum of lost opps     Formula
Opportunity owner                70000                       50000                           30000                 0.5

What would be the formula for all 3 sum columns?

Sorry for the basic question since i'm not too technically inclined.
Jimmy TramJimmy Tram
this would be handy if SFDC actually migrated the pictures over okay!
Tonya HarrisTonya Harris
I know this is over a year old; however, I have a similar request. I need to do a Win/Loss Ratio report for all of 2013 by Month then by product name. They don't want it by AE/Sales Rep but just by these that I listed. How would this be different than the above? 

Thanks, 
Tonya
Steve MolisSteve Molis
Hi @Tonya Harris 
Can you post a screenshot of the Report, Report Type, Settings, Groupings, Filters you're using and a mock-up of what you want?
Joe LewisJoe Lewis
Steve,

I can't even figure out how you get total opportunities, total won, total lost counts as you have in your report sample. This seems like it should be crazy simple but I'm clearly missing something. Can you recommends a tool to learn this stuff?
Steve MolisSteve Molis
Hi Joe,
Those are just standard fields on the Opportunity object, I'm just using a standard Opportunity Report, selecting those fields from the menu and adding them to the Report, then selecting the Closed and Won fields and Summarize(SUM) 

Have you taken any of the Online Training for building reports and using the Report Builder? =>

Quick Start : Reports & Dashboards 
Steve MolisSteve Molis
Here's another one =>
 https://help.salesforce.com/HTLaunchCourse?trainingpathid=a1S30000000X9unEAC&courseid=a1P300000011eI0EAI (https://help.salesforce.com/HTLaunchCourse?trainingpathid=a1S30000000X9unEAC&courseid=a1P300000011eI0EAI)
Joe LewisJoe Lewis
I have not had a training course for the report builder in SFDC. I have extensive experience with Seibel Analytics, which is complex, so I assumed the SFDC reporting would be more straightforward for basics like this. Clearly I was wrong on that front. I will take a look at these links you provided, and thank you for taking the time to assist me.
Steve MolisSteve Molis
Hi Joe,
I don't have any Seible, but once you review the "How to?" guides in those links, I think you'll find the SFDC Report Builder is pretty easy to get the hange of.
Joe LewisJoe Lewis
 Steve,

I am trying to replicate the following table. I continue to fail to find out how to count Opportunity/Closed/Won as the SFDC table shows.

E.g. The following Excel picture is what I am trying to do. This seems like it would be easy to do, but I cannot figure it out. I cannot even find a way to count these fields. Any suggestions would be greatly appreciated.

User-added image


User-added image


Erik PojeErik Poje
I used your advice to get a win ratio report. I am also trying to set up a demo to deal ratio, but I cannot figure it out. I want to see how many deals were closed vs how many demos were scheduled. I am having trouble because demos scheduled are in an activity report and deals closed are in an opportunity or account report.

Is there any way to get the data need for a demo to closed deal report?

Thanks,

Erik
Steve MolisSteve Molis
Hi Joe,
If you scroll through all of the posts (sorry but it's an old and a long thread by now) I mention that I am using Tom Tobin's "Power of One" technique to return the count of Opportunties => https://success.salesforce.com/0D53000001GwrJW  

From there it's pretty easy to get the Ratios using the SUM:Won / SUM:Opportunity or SUM:Closed / SUM:Oppportunity
Steve MolisSteve Molis
Erik,
I can't see into your SFDC org, so it would be really helpful if you could post screenshots of the Report, Settings, Groupings, Formulas you're using and a mock-up of what you want.
Steve MolisSteve Molis
Here's the Custom Summary Formulas that I used in that Report 
.User-added image
.User-added image
.User-added image


Matthew LohMatthew Loh

Hey @SteveMo, this is a little late but I'm wondering how you managed to get the chart here (https://success.salesforce.com/servlet/rtaImage?eid=90730000000gvTs&feoid=Body&refid=0EM30000000xlfv) to show the groupings for each sum (opportunity, closed, won).

I could only do one additional grouping, since the original grouping is by Opportunity Owner.

I can only do either the sum of opportunity, sum of closed or sum of won opportunities.

I get the formulas for the close rate ad the win/loss rate.

I swear there is a simple fix for this but it is doing my head in!

Also Happy New Year! :)

Alma GarrisonAlma Garrison
Hi @SteveMo,
I realize its been a while for this thread but I am currently in the process of setting these report formulas up and I believe I am incorrectly setting up the opportunity field formula. Would you be able to help me in setting it up for fields: IsLost & IsWon?

Thanks so much in advance!

Alma
 
Steve MolisSteve Molis
@Alma Garrison

No problem, what specifically are you stuck with? 

IsWon and IsClosed are standard Opportunity field that are automaticallt set by the Opportunity Stage. 

I also created custom fields for Lost and Open to make reports easier to read and filter 

FieldName: Open 
Datatype = Formula
Result = Checkbox 
Formula =
IsClosed = FALSE

FieldName: Lost 
Datatype = Formula
Result = Checkbox 
Formula =
AND(IsClosed = TRUE, 
IsWon = FALSE)

 
 
Steve MolisSteve Molis
@Alma Garrison

Also, can you post a screenshot of the Report, and the Groupings, Settings, etc,  that you're currently using and the results you're getting, and create a mock-up of what you would like your Report results to look like?  For questions related to Reports and Dashboards those are really helpful 

Thanks
Alma GarrisonAlma Garrison
@SteveMo Thanks so much for responding so quickly! Basically, I have 3 reports that need to show the win/lost ratio for our sales team. I’ve grouped the report by program consultant and created the 2 formulas “Win Raito” and “Won vs Lost”. So far all is showing 0%. Any and all help would be very much appreciated! Alma
Alma GarrisonAlma Garrison
here are a few screenshots
Alma GarrisonAlma Garrison
User-added image
Steve MolisSteve Molis
No problem Alma, can you post screenshots of the Reports and the Formulas you're using?  Like I said before, for questions related to Reports those are really helpful.   
Alma GarrisonAlma Garrison
User-added image
Alma GarrisonAlma Garrison
User-added image
Alma GarrisonAlma Garrison
@SteveMo
sorry, computer is a bit slow this morning!
Steve MolisSteve Molis
No problem, lemme take a crack at it
 
Steve MolisSteve Molis
So for your Win Rate% and Loss Rate% do you want to count them against ALL of the Opportunties in the Report, like

Report Total Opportunties = 100 
Won = 25
Lost = 15
Win Rate (25/100) = 25%
Loss Rate (15/100) = 15% 

Or do you want to count them against only the Closed the Opportunties in the Report, like:

Report Total Opportunties = 100 
Closed = 40
Won = 25
Lost = 15
Win Rate (25/40) = 62.5%
Loss Rate (15/40) = 37.5% 

Some people prefer this method since it give you the Win/Loss Rate% of the deals that have been decided/closed 

You can do both of them (some people prefer that too)
Alma GarrisonAlma Garrison
Thanks so much! I think the latter better suits us:)
Steve MolisSteve Molis
Okay here you go (remember to use the Insert Summary button in the Formula, don't type the Field Names)

Win Rate% (overall)
WON:SUM / Opportunity.Opportunity__c:SUM

Loss Rate% (overall)
Opportunity.IsLost__c:SUM / Opportunity.Opportunity__c:SUM
or
(CLOSED:SUM - WON:SUM)  / Opportunity.Opportunity__c:SUM

Win Rate% (closed)
WON:SUM / CLOSED:SUM 

Loss Rate% (closed)
Opportunity.IsLost__c:SUM / CLOSED:SUM
or 
(CLOSED:SUM - WON:SUM)   / CLOSED:SUM

User-added image
Alma GarrisonAlma Garrison
thanks so much for your help!!
unfortunately, I seem to be getting error messages on the last 2:
User-added image
Steve MolisSteve Molis
Can you post a nore complete screenshot?  Also incluse all of the fields that you're counting, Opportunity, Closed, Won, Open, Lost.  and select Summarize(Sum) on each of them in the Report Builder. 

a #Error Result usually means you're "breaking the Laws of Math" by dividing by 0 or NULL or something like that.  But I can only guess from that tiny chunk of the Report
Alma GarrisonAlma Garrison
my bad!
User-added image
Steve MolisSteve Molis
Thanks!

Yeah that's what I figured, you have 0 Closed Opportunities in thatv Report, so that means 0 Wins, 0 Lost, and 

WON:SUM / CLOSED:SUM = ( 0 / 0 ) which gives you a #Error 

same goes for the other Formulas
Steve MolisSteve Molis
If you don't want to see the #Error you'll need to add an Exit for you Formulas (all of them) like this 
 
IF(CLOSED:SUM = 0, 
0 ,
( WON:SUM / CLOSED:SUM )
)
or 
IF(CLOSED:SUM = 0, 
NULL ,
( WON:SUM / CLOSED:SUM )
)


 
Steve MolisSteve Molis
btw - Which Report Type are you using?  Does that Report include other objects besides the Opportunity that have fields names "Closed" on them?  Make sure you're using the Closed and Won fields from the Opportunity object in your Formula
Alma GarrisonAlma Garrison
thansk so much Steve!
It looks like the errors have gone away but I must have missed a step on the field formulas. Everything is checked as open but the WON field is not at all:( (**i made sure to check that i was pulling the correct fields in the opp object)
User-added image
Steve MolisSteve Molis
Can you edit the Opportunity Stage "Won" and verify that it is set to Type: Closed/Won? 

User-added image
Alma GarrisonAlma Garrison
ugh!
I am annoyed and frustrated that it was staring right at me!!
More importantly though, I am so very appreciative of your help!! Thank you so much and so sorry to have taken up so much of your time!

 
Steve MolisSteve Molis
Wait...  what was it?
Alma GarrisonAlma Garrison
LOL my bad! Got too excited!
The Opportunity Stage Type was set as Open not Closed/Won
Steve MolisSteve Molis
No problem, want me to close out your Tab at @Who owes me a beer or leave it open? ;-D
Alma GarrisonAlma Garrison
HAHA!
Sadly, I am sure I will be needing your services again, so Tab open!

thanks again Steve:)
Steve MolisSteve Molis
No problem Alma, it's always "Happy Hour" at @Who owes me a beer , so answers are half price.
Alma GarrisonAlma Garrison
Hi Steve, I told ya I would be back sooner than later. Ugh, these formulas are gonna make me throw my compy out the window!! Anyway, I was hoping you could help me out. Basically, a custom object was created for one of our services and my manager would like to see a comparison/average/table showing number of clients with the service active vs cancelled per month. Is that possible? Hopefully that made sense! Thanks so much in advance!! Alma
Vivian PatelVivian Patel

@Steve Molis

Hi, I know it's been a while since this thread was active. But I'm having some issues and you're very knowledgable! 

I was having the same #Error Message for Win Rate % (closed) and Loss Rate % (closed). I tried the suggestion you gave, but it doesn't appear to work for me. Any ideas? 

User-added image

User-added image





 

Steve MolisSteve Molis
Hi Vivian Patel,

Can you copy & paste your Formula rather than just posting an images of it?  It's kind of a pain to try to write out by haand and debug it
Vivian PatelVivian Patel
Yes! My bad! 

Win % Rate (closed)
WON:SUM/CLOSED:SUM

IF(CLOSED:SUM = 0, 0 , ( WON:SUM / CLOSED:SUM ) )

Loss & Rate (closed)
(CLOSED:SUM - WON:SUM)/CLOSED:SUM

IF(CLOSED:SUM = 0, 0 , ( WON:SUM / CLOSED:SUM ) )

 
Steve MolisSteve Molis
Thanks Vivian,  okay the problem is that both of your Formulas are missing an "exit" if your Divisor = NULL or 0
Dividing by a 0 or NULL breaks the Laws of Math, so you get a #Error result

Try these
 
IF(CLOSED:SUM = 0, 0 , ( WON:SUM / CLOSED:SUM ) )
 
IF(CLOSED:SUM = 0, 0 , (CLOSED:SUM - WON:SUM) / CLOSED:SUM )
Vivian PatelVivian Patel
@Steve Beautiful!! Also, how did you get that chart to appear at the bottom of your page? I have no chart at all on mine.

Thanks!! 
Steve MolisSteve Molis
Vivian,
Can you post screenshots of what you see in gthe Report Builder?  I can't really see what is on your screens from where I am sitting, so you need to post screenshots (or wait for me to just guess what the righ answer is)
Steve MolisSteve Molis
Vivian,
Can you post screenshots of what you see in gthe Report Builder?  I can't really see what is on your screens from where I am sitting, so you need to post screenshots (or wait for me to just guess what the correct answer is)
Vivian PatelVivian Patel

Hi Steve, 

This is the screen shot. My screen is scrolled all the way to the bottom, and nothing appears as a graph or chart. 

User-added image

Steve MolisSteve Molis
Hi Vivian, did you try clicking the "Add Chart" button?
User-added image
Vivian PatelVivian Patel
Face plam... thank you! 
Steve MolisSteve Molis
No problem, you owe me a beer though ;-p
Jackie TeravainenJackie Teravainen
Hi @Steve and everyone else here!  I have a similar question about formulas on reports.  I have a custom object and need to create a report to get a Success Rate on what has been quoted vs. won.  The report is summing a field (in case it matters, it's a "Formula (Currency)" field on the object that is summing two other fields) and then grouping that sum into two stages (Quoted and Won).  Customer wants a ratio of Sum of $Won/Sum of $Quoted.  2 questions:
1) Is there a formula field to calculate the Sum of each of the Status totals (Sum of [custom field] where Status = Quoted DIVIDED BY Sum of [custom field] where Status = Won)?
2) If values continue to move out of the Quoted status and into the Won status, this won't in theory work since the quoted number isn't fixed and will keep moving out of that stage.  So how do I capture a running total of that summarized value?
Thank you, oh wise ones...
Jackie T
Jackie TeravainenJackie Teravainen
Meant to post this:User-added image
Steve MolisSteve Molis
Hi Jackie it's kinda hard to wrap my head around the math without being able to see any of the numbers.  You you create a mock up in Excel and just fudge them so I have an idea of the results you're looking for?
Jackie TeravainenJackie Teravainen
This is what they want. By Account on their custom object, they want to see the Value (sum) of the objects by Status "Quoted" and "Won."  When I think about it though, my % will keep changing as quotes are won (or lost) and move statuses so I need the denominator to take picklist values in the "quoted, won and lost" status to get an overall total, don't I?  I didn't realize SF administration required mad skills in math!  And have no idea what that formula would be...  Does this help you or confuse things further?
 User-added image