Ask Search:
Anthony TrinhAnthony Trinh 

Win Rate % by stage?

I'm trying to figure out how to determine the win rate % of opportunities that past "Stage 2". I'm using the Opportunity History report but I don't know which fields I should add to my custom formula to complete this formula.

I also looked around here and it seems the "Power of One (https://success.salesforce.com/_ui/core/chatter/groups/GroupProfilePage?g=0F9300000001pFV&fId=0D53000001GwrJW)" technique is highly recommended but there are no step-by-step explanation on how to implement it. Could someone help me out please?
Best Answer chosen by Anthony Trinh
Steve DoddSteve Dodd
It's a Summary report, grouped by Owner...

User-added image



and this is my CSF...


User-added image
 

All Answers

Till KlotzTill Klotz
Hi Anthony,

with an extra checkbox and a bit of dataloading, this should be straightforward.

* Create a checkbox "Past Stage 2"
* Use the report of Opportunity History that you created and include the Opportunity Id, then export it.
* Use Excel to filter for all Opps that have past stage 2.
* Mark the checkbox for said Opps.
* WIth the power of 1, now create a report on Opportunity. Group by "Past Stage 2". Create a formula and divide by group level vs. row count all.

Cheers,
Till
Dnyaneshwar AghawDnyaneshwar Aghaw
Hi Anthony,
Please find similler issue : 
https://success.salesforce.com/answers?id=90630000000CrtCAAS
Steve DoddSteve Dodd
"Power of 1" is a formula (Number format) and the formula result is 1...Do that for every one of your objects and you'll thank me later.

Here are some screen-shots for creating the formula:
https://success.salesforce.com/answers?id=9063000000048RbAAI


The syntax for the custom summary formula in your Opportunity History report would be: 
WON:SUM/Opportunity.PowerOf1__c:SUM

Filter the report by To Stage equals (any Stages that are greater than "Stage 2")


User-added image
Steve DoddSteve Dodd
Here is a better screen-shot of the CSF...group your report by "To Stage" and display the % formula there



User-added image
Anthony TrinhAnthony Trinh

Thanks Steve! That helped a lot.

I'm using the "greater than stage 2" rule but it looks like what I have as stage 2 isn't actually set up as stage 2. Where in SFDC do I change the order of my stages?

My stages go from Identified Opportunity (stage 1) -> Business Case Complete (stage 2) -> Final Negotiations (stage 3) -> Verbal Agreement (stage 4) -> Won - Add to OB (stage 5) -> Closed 

I only want to track the win % of deals that meet or exceed Stage 2.

Anthony TrinhAnthony Trinh
I guess I can just filter it as From Stage equals "Business Case Complete", right?
Anthony TrinhAnthony Trinh

Now that I'm doing some data integrity... it looks like the summary formula you gave me didn't work.

I see an opportunity in my report that hasn't been won yet but is included.
User-added image

User-added image

Did I embed the formula incorrectly? (via a seperate column)

Anthony TrinhAnthony Trinh

I am really trying but it's not working :( 

Also when you said "[apply the power of 1] for every one of your objects", what is an example of an object?
I think I may have set up the Power of 1 field incorrectly. I only did one for the Opportunity page (not per "object")
User-added image

Did I not set it up correctly?

Steve DoddSteve Dodd
An object is a table in a database...so for example, one formula on Account, one formula on Opportunity, one on Contact, one on Quotes, one on Contracts...etc, etc...and do the same for any custom objects you create. It's very useful and as you dig deeper into reports, you'll find yourself using Power of 1 a lot.

The formula you want to specify in the formula letter is just this...

1

...and that's it - nothing else.


User-added image

Your CSF looks right...i think you just need to revisit your Powerof1 formula.

And in response to your question about the filter criteria, I was just using an example, but the "Greater Than" idea should work...you want to include all Opportunities, Won, Lost and pending...because you're saying show me the ratio of "Won" (By Stage) to "All" (By Stage)

The Opportunity Stages make up a business process within the Opportunity. Salesforce gives you some out-of-the box selections, but most people add or make changes to them.

You can go into the Stage field in the Opportunity object and click the Reorder button to move the selections up or down in the picklist, but training the Users on how that process works also goes along with making any changes - you want Users to select the correct Stage so your data is accurate.

A typical (or not so typical) Stage progression in an Opportunity might look like this, with the Probability % tied to each one increaing as you move up in Stage:
Prospecting
Qualification
Initial Meeting
Needs Analysis
Proposal
Negotiation/Pricing
Closed Won
(or Closed Lost)

Closed Won Opps have a probablity of 100%, Closed Lost = 0%, and everything else is somewhere in the middle.
 
Steve DoddSteve Dodd
* formula editor (not formula letter) - trying to type too fast

User-added image

 
Steve MolisSteve Molis
#PREACH it SteveD !
Anthony TrinhAnthony Trinh

Thanks SteveD for the quick response - apologies I was slow to mine. I triple checked my formulas and they seem to be set up correctly. I also do have them inputted per object.
User-added image

How would I apply the Power of 1 field to each report? I didn't do that... and I still see multiple of the same opportunities which skews the data. Any thoughts?
User-added image

Steve MolisSteve Molis
Are you lookin' for something kinda like this?

https://success.salesforce.com/answers?id=9063A000000iWbyQAE  
User-added image
User-added image
Steve DoddSteve Dodd
++SteveMo

That looks great...I left mine as a Summary report - not nearly as much sizzle as yours.


User-added image
 
Anthony TrinhAnthony Trinh

Thank you SteveM and SteveD!

I went with SteveD's route as it worked for me and was more straightforward for a beginner like me. But SteveM's chart looks great... I will bookmark this solution and see if I can recreate that in the near future.

Thanks again all!

Anthony TrinhAnthony Trinh

Actually, just to make sure. Could someone double check my logic / results? 

I got the 42% Win Rate but I'm seeing more than 1 of the same Opportunities listed in my report. How do I apply the "Power of 1" Technique?

User-added image

Steve MolisSteve Molis
If you want us to double check your logic / results, could you add your Power of One field to the Report results and post a complete screenshot of the Report, the Formula and the Settings you use so we can see all of the numbers?  
Steve DoddSteve Dodd
C'mon man, I've seen the stuff you've been posting about what you're working on...you passed "beginner" about a mile back.

Did you create a custom summary formula in your report (for Win Rate) or is that a formula field on the Opportunity ?
Anthony TrinhAnthony Trinh
Power of 1 enabled for "Opportunity":
User-added image

Power of 1 added to report:
User-added image

Custom Summary Formula for Win Rate:
User-added image

I still feel like a beginner :( only started this job for a few months... 
I think the problem with my report is that I don't know how to apply the "Power of 1" - I just drag it to the report and hope for the best? Thoughts?
Steve DoddSteve Dodd
You're doing great, and learning very quickly...don't sell yourself short - plus you know how to ask the right questions, which is awesome.

I think you should try to tackle SteveMo's matrix report.
Anthony TrinhAnthony Trinh

Thanks SteveD... that makes me feel a little better :)

SteveMo; how do I get the "current stage" field? I only see From Stage and To Stage. I think the reason I'm seeing duplicate opportunities is because I'm using the To Stage.

Steve MolisSteve Molis
That's from a set of a custom formula fields that I built on my SF org. 

I went a little Justin Timberlake and "brought the Sexy back" to Opportunity History Reports 

Tips & Tricks: Getting more out of Opportunity History Reports
https://success.salesforce.com/answers?id=90630000000DGJMAA4
Anthony TrinhAnthony Trinh
Lol! Genius.

So I set it up but I got an error message from the custom summary formula. Do you think it's because my Opportunity ID is different from yours?

User-added imageUser-added image

 
Steve MolisSteve Molis
Don't just copy&paste Formulas or type them in manually.  
Use the Insert button and the Custom Summary Menu to get the correct API Name of the Field from your own SF org and Report
Anthony TrinhAnthony Trinh

I did the SUM of the Power of 1 divided by PARENTGROUPVAL From Stage Column Grand Summary.

However, SFDC didn't recognize the summary field which it had automated for me. Any ideas?

User-added image
User-added image

Steve DoddSteve Dodd
You still have summary_field in the syntax for your CSF from where you used the <Insert button...try it like this:
 
Opportunity.PowerOf1__c:SUM
/
PARENTGROUPVAL(Opportunity.PowerOf1__c:SUM, FROM_OPPORTUNITY_STAGE_NAME,
COLUMN_GRAND_SUMMARY)


User-added image
Anthony TrinhAnthony Trinh
That did the trick!! Thanks so much. 

I forgot to add the "SUM" part before.

Cheers! :)
Anthony TrinhAnthony Trinh
Actually, Steve, how would I be able to segment this per Rep? Everywhere I move the fields seem to only show the total Win Rate as opposed to Win Rate by Rep.

User-added image
Steve MolisSteve Molis
You'll need to edit your Formula and Settings
Anthony TrinhAnthony Trinh
What would I change it as?

This is what I have currently:

Opportunity.Power_of_1__c:SUM

PARENTGROUPVAL(Opportunity.Power_of_1__c:SUM, FROM_OPPORTUNITY_STAGE_NAME, COLUMN_GRAND_SUMMARY)
Steve MolisSteve Molis
So something like CSF-B% in this Report? 
 
User-added image
Anthony TrinhAnthony Trinh
^ Yes, that's exactly what I need!
Anthony TrinhAnthony Trinh
So I got it to work and show in my report:
User-added image
User-added image

However, on my dashboard, I can't select the Win Rate % to show... just the SUM:
User-added image
User-added image
User-added image

What am I doing wrong?
Steve DoddSteve Dodd
Check the Chart in your Report

It should look like this:

X-Axis           CSF (Win Rate %)
Y-Axis           Opportunity Owner
Group By      Stage

I don't have the custom Stage field that you're using, so I can't test it...but if that doesn't work, try it with Stage as the Y-Axis and grouping by Owner
Steve MolisSteve Molis
++ SteveD 

I'm in a Lightning Now workshop today + tomorrow, so I'm not gonna be online much.  SteveD can guide you in for landing.
Anthony TrinhAnthony Trinh

Thanks both Steves!

SteveD, as seen from the last screenshot in my last post, I actually can't edit the X or Y Axis. This was the problem I had. Here's what happens when I add the Win Rate % in for a graph chart:
User-added image

But I want a Leaderboard-style chart, not a graph chart. But my Leaderboard chart only shows the Quantity field, and not the Win % field. 

Steve DoddSteve Dodd
What do you mean when you say you can't edit each Axis ? You can't change the drop-down selections in the Component Editor ? You just want to click the down arrow in each field.


User-added image


Select Table as your Component Type
Put Win Rate % in the X-Axis
Put Owner in the Y-Axis
 
Anthony TrinhAnthony Trinh
Yeah, but when I click it, nothing happens:
User-added image

Also, I'm only able to change the axises for bar graphs... but I want to have a Leaderboard style chart. Is this possible? I feel like it must be
Steve DoddSteve Dodd
Yes, you can have a Leaderboard, you would just use a Table (the one on the far right) instead of a bar chart

I think you might be clicking in the wrong place - try clicking on the arrow to the right of where it says Win Rate %, just under the Formatting tab in the Editor screen.
Anthony TrinhAnthony Trinh
Mhm that's the Table I've had all along but 1) cannot change axises 2) only shows Quantity of Wins as opposed to Win Rate %

See below my Formatting tab:
User-added image

I don't see Win Rate % anywhere?
Steve DoddSteve Dodd
Can you post a screen shot of the Chart Editor screen (Chart Data tab) from your report ?
Anthony TrinhAnthony Trinh
Sorry, where do I find the Chart Editor screen? It's not any of the screenshots I posted earlier?
Steve DoddSteve Dodd
No problem...you would just click on the Edit Chart link in your report


User-added image

 
Anthony TrinhAnthony Trinh
Ah gotcha. Please see below:
User-added image

Chart is still not showing any results for Win Rate %... despite me actually seeing it correctly in the report itself
Steve DoddSteve Dodd
Try closing the chart, then change the report type from Matrix to Summary, then back to Matrix - you should see some information in your chart.

But the way you have each Axis setup on your report chart looks correct.

You may have the groupings setup incorrectly in your matrix...try moving the Owner field to where you have the PowerOf1 field grouped (and vice-versa).
Anthony TrinhAnthony Trinh
I tried to close the chart / change the report type, but still, no information popped up.

Do you mean regrouping it like this? This doesn't give me the Win Rate % by Rep, unfortunately. 
User-added image

If I move the PowerOf1 field to the left side, the Win Rate % doesn't work anymore.

Man, my report works but I just can't figure it out on the Dashboard...
Steve DoddSteve Dodd
I'm able to display a table component in a dashboard with the Owner as one column and the Win Rate% as a second column.


I've got my report like this...

User-added image


...my report Chart like this

User-added image

...and my Dash component like this

User-added image


...but I don't get any data

If I move "From Stage" from a Column grouping in the report to a Row grouping, then I get data...but I lose my Win Rate % CSF

Is that the same thing that's happening to you ?
 
Steve DoddSteve Dodd
You won't get anything in your report Chart unless you have a Number in one of the Axis fields...either Record Count or PowerOf1 (SUM)
Anthony TrinhAnthony Trinh
Yeah that's exactly what is happening to me. The only difference between our dash component is you're able to see your "Table Columns", which I don't:
User-added image

Are you saying you were able to see the Win Rate % when you created it in the Dashboard?
Steve DoddSteve Dodd
I was able to specify the field as a column in the dashboard table (your Component Editor is still wrong, by the way), but without data because the report chart is empty.

You may need to create one report with the row and column groupings the way you want to display them for your Users - without a report chart...then create a second report that uses different groupings - and includes a report chart, and use that version of the report to drive your dashboard component.

You want the Formatting tab of your dashboard Component Editor to look like this...with the correct column names:

User-added image
 
Anthony TrinhAnthony Trinh
Hi Steve,

Sorry for the late response - I attempted this and failed and left it for another time... which is today but still can't figure it out. My Component Editor matches yours now but even when Win Rate % is selected as a column, I don't see anything on the dashboard.

As a refresher, I got my report working with the Win Rate % but can't get the Win Rate % to show on my dashboard as it does on my report:
User-added image

Dashboard:
User-added image

Not sure I follow what you meant by creating two separate reports?
 
Steve DoddSteve Dodd
When I mentioned the two different versions of the same report, I think I may have just been suggesting a workaround. If you are a fan of the way the report displays, and you don't want to change the row or column groupings to make the dashboard appear the way you need it to...then use one version of the report that you can show to Users or to Management, and a different version that drives the dashboard component.

But...bottom line...the dashboard won't work until you setup the row and column groupings correctly.

You need to group your source report by Owner and by Win Rate %, then add a chart to that report that uses those groupings...then go back into the dashboard component and specify the columns that the Table needs to display.
Anthony TrinhAnthony Trinh
Ok I understand what you mean now. I only need to show it to Management, so just a version that drives the dashboard component.

How do I group my source report by Win Rate % without using Current Stage? Doesn't the Win Rate % formula have to have those fields in order to process the formula?
User-added image

And I assume the Power of 1 / record count field is needed too.
Steve DoddSteve Dodd
The Stage needs to be a column in the report since your CSF is using that field...and record count is there by virtue of the fact that you have at least one row.


But the report chart would need to be grouped by both Win Rate and Owner - you just want this, right ?



Report Chart:



User-added image





Dashboard Component:




User-added image




User-added image

 
Anthony TrinhAnthony Trinh
Steve; yes that's exactly what I want (although in a leaderboard chart format).

Could you please show me how your report looks like? and your Win Rate formula?

This is what mine looks like, and I can't recreate the Win Rate % if I remove any rows/columns so I'm not sure what I'm doing wrong:
User-added image

Win Rate formula:
User-added image

This is driving me crazy! :( 
Thanks for the help.
Steve DoddSteve Dodd
It's a Summary report, grouped by Owner...

User-added image



and this is my CSF...


User-added image
 
This was selected as the best answer
Anthony TrinhAnthony Trinh
SteveD; you are amazing. Thanks for your continued support... this is very helpful for our company.

Changing my report from Matrix to Summary made a huge difference. 

Cheers!
Anthony TrinhAnthony Trinh
Actually, SteveD, could you please explain your formula to me? I'm starting to doubt my Win Rate %.

Ron's win rate is 9%, which I don't believe is true. I'm trying to calculate the Win Rate for opportunities after the "Business Case Complete" stage.

My report filters:
User-added image
His opportunities within last 6 months:
User-added image

He closed all his opportunities within the last 6 months... so shouldn't his win rate be 100%?
Steve DoddSteve Dodd
I thought the problem you were having was with the dashboard table and not the formula itself - the CSF I was using was really just for illustration purposes.

But I don't think you need anything more than this if you just want to show the ratio of "Won" Opportunities to all Opportunities owned...
WON:SUM
/
Opportunity.PowerOf1__c:SUM



This formula calculation will be displayed in the report at the level you select:
Grouping 1: Owner

 
Anthony TrinhAnthony Trinh
I think because I changed the dashboard, the formula I had no longer worked since it was depending on fields that I removed. But what you put above works :)

Thanks so much!! I will stop bothering you now. Have a great weekend!

Cheers,
Anthony
Steve DoddSteve Dodd
No bother at all...glad to hear it's working now
Lavanya RLavanya R
Hello

I am looking to get all opportunities by stage that were in pipeline during a past period say Q1, and find the win rate as of the end of that quarter. It should also include any open opportunities that had no stage change during that period.

So, I have taken all opportunities that were created during or earlier than Q1, and closed during or later than Q1. 
We want Win rate to be calculated as sum of won opportunities out of all the opportunities (open/closed) during that time. 

Win Rate %: 
WON:SUM
/
Opportunity.Opportunity_Count__c:SUM

I believe this would tell how many of those opportunities are won at present, but we want to know the win rate as of end of Q1. Appreciate any help on how we can get that.  

Opportunity Count is my power of one field. 
User-added image

Thanks!