How do I summarize Opportunity Amounts by Forecast Category in Accounts? - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Hans-Wolf ColsmanHans-Wolf Colsman 

How do I summarize Opportunity Amounts by Forecast Category in Accounts?

Hello,

I would like to have six formula fields in my Account Layout, summarizing all related Opportunity Amounts by Forecast Category:

Sum Commit
Sum Best Case
Sum Pipeline
Sum Closed Won
Sum Closed Lost
Sum Ommitted (without Closed Lost values)*

* I am not sure if in the Forecast Category "Ommitted" the "Closed Lost" Amounts are counted or not.

Do you have an idea how to set this up?
Looking foreward to your advice,
Thanks,

Hans-Wolf


Deepak AnandDeepak Anand
Hans,

I think you should get around them by creating a couple of Rollup Summary fields at the Account level summarizing the Amount field on the Opportunity. Take a look at this: http://help.salesforce.com/HTViewHelpDoc?id=fields_defining_summary_fields.htm&language=en_US
Jerry ReidJerry Reid
You can create roll-up summary fields on the account object to accomplish this.
Hans-Wolf ColsmanHans-Wolf Colsman
Deepak, Jerry,
Thanks for your advice. Rollup Summary does work to get a sum of all Opportunity Amounts, but I do not understand how I get a filter included?

I manage to get the numbers sorted in a Dashboard, but for better reporting, I still like to have a field for each number.

 e.g. Account Layout 
"Sum Pipeline" = 1.541.000 
"Sum Best Case" = 3.445.000




Hans-Wolf ColsmanHans-Wolf Colsman
User-added image
Jerry ReidJerry Reid
Hans,

You can setup a multiple filters for each roll-up summary field, including selecting one or more options for Forecast Category field. See image below...
User-added image
Hans-Wolf ColsmanHans-Wolf Colsman
Jerry,

How do you get Amount in the "Field to Aggregate" ? Non of my currency value fields appears in the choice.  
Jerry ReidJerry Reid
Do you have multi-currency enabled?
Hans-Wolf ColsmanHans-Wolf Colsman
Yes, Euro as Main Currancy. USD as second
Hans-Wolf ColsmanHans-Wolf Colsman
With "Quantities" it runs perfectly well.
Do you have an idea how I could create a field "Euro Amount" and a "Dollar Amount" field that just takes the value but does not have the currency?

Jerry ReidJerry Reid
Hans, unfortunately, I believe you have run into a limitation of using roll-up summary fields when advanced currency management is enabled. Here is a document describing roll-up summary fields & their limitations; you will see that there are quite a few limitations in using them natively within Salesforce, http://help.salesforce.com/HTViewHelpDoc?id=fields_about_roll_up_summary_fields.htm&language=en_US

Here is what is says specifically, "If your organization has advanced currency management enabled, currency roll-up summary fields are invalid if they are on accounts and summarizing opportunity values, or on opportunities and summarizing custom object values."

You have two options to solve this problem:
1) Write Apex Code to perform the rollups, but this is going to get complicated because you have multi-currency enabled
2) Use the application, Rollup Helper (http://appexchange.salesforce.com/listingDetail?listingId=a0N30000009i3UpEAI), which supports basic and advanced (dated) currency managment & currency rollups. It will also overcome all the other limitations listed in the Salesforce document I referenced.