Behold the Power of Salesforce Report Summary Formulas, Part III!

In our previous posts, we showed you how you could calculate a 3-month moving average of Closed Won Opportunities (Part I) as well as figuring out the conversion rate of all leads over a period of time (Part II). In this installment, we’ll cover the third example: Determining the percentage of business a sales rep brings in.

First, we need to create a new report on Opportunities.

Then, we need to set some criteria for our report – changing the Opportunity Status to “Closed Won” the Date Field to “Close Date” and select a date range.

Next, change your report format over from a Tabular report to a Summary report and use Opportunity Owner as the grouping field for your report.

 

Lastly, as we have done in the previous posts of this series, we need to add a new Report Summary Formula to calculate the percentage of closed won opportunities that each person has brought in. To do this, click on the Add Formula link.

 

When creating the formula, you will need to give it a name and determine its format. For this scenario, we chose Percent as the format with two decimal places. Since we have a grouping setup on our report using the Opportunity Owner field, 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 the Opportunity Owner grouping is selected. Lastly, we need to provide the formula that will do all the heavy lifting. Use the following for your formula:

RowCount/PARENTGROUPVAL(RowCount, GRAND_SUMMARY)

What this formula says is this: – Take the row count of the Opportunity Owner group, – Then divide that number by the row count in the Grand Summary of the Parent grouping (in our case, the total number of all closed won opportunities).

Here is a visual of what your Custom Summary Formula should look like once saved:

 

Once you save this Formula, run your report, hide the details, and find our which salesperson is your champion!

 

But, this isn’t the whole picture yet. This formula is only calculating the number of closed won opportunities by record count. To be fair, you should probably be looking at the value that each sales person bring in against the total of all opportunities. To do that, let’s head back into editing our report through the report builder.

Make sure you are showing details in case the Amount field is already set as one of your report columns.

 

If the Amount field is not already on your report, locate it in your field list and add it to your report. Then, while hovering over the Amount column name, click on the dropdown arrow and select “Summarize this field”. Then select “Sum” and click on Apply.

 

This will display a summary of the Amounts for each of our grouping (in this case Opportunity Owner). This step isn’t needed to perform the calculation that we will be doing next on the Amount field, however it’s a visual reminder of how we will be arriving at our final percentages.

Next, we need to tweak our formula to instead look at the total Amounts of each group, rather than the record counts. To do this, we need to change our formula to the following:

AMOUNT:SUM/PARENTGROUPVAL(AMOUNT:SUM, GRAND_SUMMARY)

We essentially replaced both instances of “RowCount” with “Amount:SUM”, which now says to take the sum of the Amount field for our grouping and divide it by the sum of the Amount at the Grand Summary level. This in turn will now compare the Amounts of the Opportunities for each sales person against the company as a whole. Save your change to the formula and run your report – don’t forget to hide the details so you can just see the summarized information.

 

Now that’s better! It turns out that both sales people are neck-and-neck when we compare the Amounts of their Closed Won Opportunities. Good thing we took those extra steps to compare how much business our sales reps were bringing in!

For more Salesforce blog posts be sure to click here and also be sure to subscribe to our blog on the top right of this page to receive email notification when new blogs are posted!

 

Fill out the form below to receive instant access to our On-Demand presentation on “Making the Transition to Salesforce Lightning”!

About Ledgeview Partners

Comments are closed.