### Browse by categories

- All
- Collaboration
- Configuration & Data Management
- CPQ and Billing
- Customer Service & Support
- Desktop Integration
- Einstein Analytics
- Email Marketing
- Journey Management
- Mobile
- Mobile Messaging
- Packaging, Uploading & Installing Apps
- Reports & Dashboards
- Sales & Marketing
- Security
- Social Marketing
- Trailhead Challenges
- Additional Products

Ruth MargolisAfter 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 MolisYou 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:

Ruth MargolisI'm fooling with a Custom Report Summary Formula, the problem is getting it to recognize that Status has different counts for different results. Ruth MargolisGot 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 MolisThese are the formulas I used in that Report

Steve MolisDamn you! @Screamapillar I woulda beat you if I hadn't posted screenshots! ;-p Ruth MargolisAnd @SteveMo trumps me again. Javier GonzalezThis 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 StevensThank 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 MolisHi Greg, all of the field that I refernced ar standard SFDC Opportunity Fields. Which one can you not find? Greg 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.

Steve MolisThat'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 StevensIncredibly 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 GoldenCan 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 DickensCan 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 MolisHi 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 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).

Lam Aik PeaI 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 Tramthis would be handy if SFDC actually migrated the pictures over okay! Tonya HarrisI 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 MolisHi @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 LewisSteve,

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 MolisHi 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 & DashboardsSteve MolisHere's another one => https://help.salesforce.com/HTLaunchCourse?trainingpathid=a1S30000000X9unEAC&courseid=a1P300000011eI0EAI (https://help.salesforce.com/HTLaunchCourse?trainingpathid=a1S30000000X9unEAC&courseid=a1P300000011eI0EAI)

Joe LewisI 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 MolisHi 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 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.

Erik PojeI 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 MolisHi 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 MolisErik, 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 MolisHere's the Custom Summary Formulas that I used in that Report

.

.

.

Matthew LohHey @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 GarrisonHi @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 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 =

FieldName: Lost

Datatype = Formula

Result = Checkbox

Formula =

Steve 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 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 GarrisonSteve MolisNo 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 GarrisonAlma Garrison@SteveMo sorry, computer is a bit slow this morning!

Steve MolisNo problem, lemme take a crack at it

Steve MolisSo 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 GarrisonThanks so much! I think the latter better suits us:) Steve MolisOkay here you go (remember to use the Insert Summary button in the Formula, don't type the Field Names)

Win Rate% (overall)

Loss Rate% (overall) or

Win Rate% (closed)

Loss Rate% (closed) or

Alma Garrisonthanks so much for your help!!

unfortunately, I seem to be getting error messages on the last 2:

Steve MolisCan 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 Garrisonmy bad!

Steve MolisThanks!

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 MolisIf you don't want to see the #Error you'll need to add an Exit for you Formulas (all of them) like this

or

Steve Molisbtw - 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 Garrisonthansk 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)

Steve MolisCan you edit the Opportunity Stage "Won" and verify that it is set to Type: Closed/Won?

Alma Garrisonugh!

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 MolisWait... what was it? Alma GarrisonLOL my bad! Got too excited! The Opportunity Stage Type was set as Open not Closed/Won

Steve MolisNo problem, want me to close out your Tab at or leave it open? ;-D@Who owes me a beerAlma GarrisonHAHA!

Sadly, I am sure I will be needing your services again, so Tab open!

thanks again Steve:)

Steve MolisNo problem Alma, it's always "Happy Hour" at @Who owes me a beer ,so answers are half price.Alma GarrisonHi 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 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?

Steve MolisHi 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 PatelYes! 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 MolisThanks 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

Vivian 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 MolisVivian, 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 MolisVivian, 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 PatelHi Steve,

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

Steve MolisHi Vivian, did you try clicking the "Add Chart" button?

Vivian PatelFace plam... thank you! Steve MolisNo problem, you owe me a beer though ;-p Jackie TeravainenHi @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 TeravainenMeant to post this: Steve MolisHi 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 TeravainenThis 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?

Shilpa SrikantacharHi All ,

I see a long thread on this question and it confused me . So , I thought to repost this question so that I could get clear answer to the Report I am trying to build . I need to build a "% ratio (Closed projects Won divided by all closed projects YTD)" report . Please help me with this .

Thank you

Shilpa

Steve MolisShilpa Srikantachar Can you post a screenshot of the Report, Settings, and Filters that you're using, the results you're getting, and a mock-up of what you want? For questions related to Reports and Dashboards those are really helpful (otherwise it's like playing "Pin the Tail on the Donkey")

Steve MolisPS. @Jackie Teravainen are you trying to do something kinda like this?