3

Example of summary formula I'm working on

Take a look at the matrix report screenshot from above.

What we've got here is a custom object for forecasting, with a custom picklist called "record type" that differentiates between our Budget (ie our sales goal) and our actual won / open opp totals.

The goal here is to show the difference between the 'Budget' grouping and the 'Closed Won' grouping per time period. The highlighted row shows the meaningful part of the summary formula I created: Our 'budget difference' for May 2014 was $848k above our Budget amount of $310k.

The problem with my formula as it stands is that it shows difference between ALL grouping types (diff between budget and closed won, then diff between closed won and open, etc).

Ideally, I'd like the 'Budget Difference' summary formula to ONLY calculate differences between the "Budget" and the "Closed Won" groupings. Even more ideal would be to have that field only display once in the Closed Won group, as opposed to in every group. Is that possible?

My summary formula:

Sales_vs_Budget_Reporting__c.Amount__c:SUM - 
PREVGROUPVAL(Sales_vs_Budget_Reporting__c.Amount__c:SUM,
Sales_vs_Budget_Reporting__c.Record_Type__c,1)

As I understand it, PREVGROUPVAL lets me access summary values from other grouping values in the same grouping level (in this case Record Type). So I basically say take the sum from each record type group and subtract from it the sum from the previous grouping. That works for the "closed won' group because Budget happens to be right before it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.