Now, we’re not talking about formula fields, the kind you configure as a custom field on an object to perform some type of calculation and store that value at the record level. We are talking about formulas in reports that perform calculations on the summaries you’ve defined within the report itself.
When would you ever consider using one of these reporting formulas? When you think you need to export your report results to Excel to perform some crazy calculation, that’s when!
Here are some examples of when you could be using reporting formulas:
- Calculate a 3-month moving average of Closed Won Opportunities
- Figuring out the Conversion rate of all leads over a period of time
- Determining the percentage of business a sales rep brings in
Let’s run through the first one of the examples above, calculating the 3-month moving average of your closed opportunities.
First, we will create a report on Opportunities that are Closed Won. If you decide to focus on a time frame, keep in mind that the report will need at least 3 months’ worth of data to start plotting your 3-month moving average.
In report builder, add whichever fields you would like to your report, but make sure you add the Amount and Close Date fields.
Ensure that you have switched your report over from a Tabular Report to a Summary Report, and that you are grouping your Opportunities together by the Close Date field, grouped by Calendar Month.
If you add a chart at this point, you’ll have a nice bar chart that shows your Opportunities value for each month.
But we’re not done yet. We can also add in a 3-month moving average line to the chart to show how the business is trending.
To do this, we need to create a Report Summary Formula field. Back in report builder, in the Fields menu on the left, double click on “Add Formula”.
If this option is not showing, it is most likely because your report is still set for Tabular Format – once you switch your report to Summary, Matrix, or Joined, you will see the option for Add Formula.
When creating the formula, you will need to give it a name and determine its format. For this scenario, we chose Currency as the format with zero decimal places. Since we have a grouping setup on our report for Close Date, we are presented with the option of where to display the results of the formula.
This is an important step as the location of the formula result will determine what summary values the formula can access in the report.
Make sure that your Close Date (by Calendar Month) grouping is selected. Lastly, we need to provide the formula that will do all the heavy lifting.
Use the following for your formula:
PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE,1) +
What this formula says is this:
– Take the sum of the Amount field for this current grouped Close Date, plus,
– The sum of the Amount field of the first previously grouped Close Date in the report, plus,
– The sum of the Amount field of the second previously grouped Close Date in the report,
– Then divide the total by 3.
This will be easier to see that to read, so here is a visual of what your Custom Summary Formula should look like once saved:
Once you save this Formula, it will appear at the very end of your columns – report summary formulas will always be the last columns in your report. Also, since we are averaging 3 months’ worth of data to get our average, the first two months on the report will not have a value.
Once you save your formula and run your report, you should see something like the following:
Now for the final act – visualizing that moving average in the chart! Go back into Customize, edit your chart, and select the “Plot additional values” in the Combination Charts section. Use Line as your Display, and make sure the report summary field you created is selected for the Value.
Now, when you run your report, you’ll be displaying not only the monthly totals of your opportunities, but the rolling 3-month average of those opportunities as well!
You can check out more of our Salesforce Tips in our blog archive. Please contact Ledgeview Partners if you or your Salesforce Administrator need assistance to ensure that you are getting your optimal use from your Salesforce investment.