Tips & Tricks: Getting more out of Opportunity Report Formulas - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Steve MolisSteve Molis 

Tips & Tricks: Getting more out of Opportunity Report Formulas

Here's a few custom Formula Fields that I created in my Salesforce org to make building Report Formulas a lot easier and to get around some of the limitations of Row-Level and Summary Level Formulas

Enjoy

SteveMo

FieldName: Open 
Datatype: Formula(Checkbox)
Formula: 
IsClosed = FALSE

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

FieldName: $Amount(Closed)
Datatype: Formula(Currency)
Formula:
IF( IsClosed = TRUE , Amount , 0 )

FieldName: $Amount(Open)  
Datatype: Formula(Currency)
Formula:
IF( IsClosed = FALSE , Amount , 0 )

FieldName: $Amount(Won)  
Datatype: Formula(Currency)
Formula:
IF( IsWon = TRUE , Amount , 0 )

FieldName: $Amount(Lost)
Datatype: Formula(Currency)
Formula:
IF( AND( IsClosed = TRUE , IsWon = FALSE ), Amount , 0 )
Dominic CarubbaDominic Carubba
Answer: Who is the #AwesomeAdmin @SteveMolis
Buyan thyagarajandBuyan thyagarajand
@Steve Molis Thanks for sharing this. I had done a blog on your formula fields before and would like to know if you are okay with me creating a blog on my site and referencing your name? Also please let me know how do we access your org to get these formula fields?
Steve MolisSteve Molis
@Buyan thyagarajand 

Sure! no problem
Steve MolisSteve Molis
Some of you might be asking "How can I use these in my org?" 
 
Great question!  
 
Suppose you need to display the count of Open Opportunities in a Report, but you don't want to burn a Report Grouping by grouping it by Opportunity Stage, because you need them for other stuff like Opportunity Owner, or Account Name, or Product Name, or CloseDate
 
 
FieldName: Open
Datatype: Formula(Checkbox)
Formula:
IsClosed = FALSE
 
Or maybe you need to display the count, instead of having to subtract the Count of Won Opportunities from the count of Closed Opportunities this: ( CLOSED:SUM - WON:SUM ) 
 
FieldName: Lost
Datatype: Formula(Checkbox)
Formula:
AND( IsClosed = TRUE , IsWon = FALSE )

You can just grab your shiny new Lost checkbox field and add it to the Report.  And if you need to calculate your Loss Rate % your Report Formula because as simple as ( Opportunity.IsLost__c:SUM / CLOSED:SUM ) 

And instead of using up your one and only Row-Level Report Formula, with these Fields you can easily display the % of Open Revenue  
 
( Opportunity.Amount_Open__c:SUM / AMOUNT:SUM )

Or the $ Revenue Win Rate %   
( Opportunity.Amount_Won__c:SUM / Opportunity.Amount_Closed__c:SUM )

Or the  $ Revenue Close Rate %
( Opportunity.Amount_Closed__c:SUM / AMOUNT:SUM )

Or $ Revenue Loss Rate %      
( Opportunity.Amount_Lost__c:SUM / Opportunity.Amount_Closed__c:SUM )
 
FieldName: $Amount(Closed)
Datatype: Formula(Currency)
Formula:
IF( IsClosed = TRUE , Amount , 0 )

FieldName: $Amount(Open)  
Datatype: Formula(Currency)
Formula:
IF( IsClosed = FALSE , Amount , 0 )

FieldName: $Amount(Won)  
Datatype: Formula(Currency)
Formula:
IF( IsWon = TRUE , Amount , 0 )

FieldName: $Amount(Lost)
Datatype: Formula(Currency)
Formula:
IF( AND( IsClosed = TRUE , IsWon = FALSE ), Amount , 0 )

Then you can get a Report like this 
User-added image 

(and I still didn't have to use up my 1 Row-Level Formula to build it)
Adivishnu NandamAdivishnu Nandam
Awesome, Pretty neat. Thanks @Stevemolis