### 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

# Tips & Tricks: Getting more out of Opportunity Report Formulas

Enjoy

SteveMo

Datatype: Formula(Checkbox)

Formula:

IsClosed = FALSE

Datatype: Formula(Checkbox)

Formula:

AND( IsClosed = TRUE , IsWon = FALSE )

Datatype: Formula(Currency)

Formula:

IF( IsClosed = TRUE , Amount , 0 )

Datatype: Formula(Currency)

Formula:

IF( IsClosed = FALSE , Amount , 0 )

Datatype: Formula(Currency)

Formula:

IF( IsWon = TRUE , Amount , 0 )

Datatype: Formula(Currency)

Formula:

IF( AND( IsClosed = TRUE , IsWon = FALSE ), Amount , 0 )

__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 CarubbaAnswer: Who is the #AwesomeAdmin @SteveMolis Buyan 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 Molis@Buyan thyagarajand

Sure! no problem

Steve MolisSome 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

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:
FieldName: Lost

FieldName: $Amount(Closed)

Datatype: Formula(Checkbox)

Formula:

IsClosed = FALSE( CLOSED:SUM - WON:SUM )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 )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

(and I still didn't have to use up my 1 Row-Level Formula to build it)

Adivishnu NandamAwesome, Pretty neat. Thanks @Stevemolis