Posted by Megan Leary, Senior Consultant
Wave Analytics is the shiny new toy…and admittedly very cool. But we must not forget the tool that made real-time, reliable and easily attainable data possible – the Salesforce Report Builder! Report Builder has evolved over the years and new capabilities have been added since its inception. One of these additions, Summary Formulas, has been around for a while, but I often find that administrators are not always taking advantage of this time-saving feature. If you are exporting reports in Excel to do additional math on the data, you might be missing a Summary Formula opportunity! Here are my three favorite uses for Summary Formulas:
1. The many uses of PARENTGROUPVAL and PREVGROUPVAL Functions
The PARENTGROUPVAL function allows you to do math on a value against a total value of a grouping to which it belongs.
Use Case: Find the percent of total of each Product sold this month:
With my simple Summary Formula, I can see with a quick glance that of all my units sold, 23% of them were the “Gasoline 300kW” Product.
Formula used in the example:
QUANTITY:SUM/PARENTGROUPVAL(QUANTITY:SUM, GRAND_SUMMARY)
The PREVGROUPVAL function will compare values to a previous, peer grouping.
Use Case: You want to see the difference in the Amount of Closed Opportunities from month to month.
A simple Summary Formula does the math you need right in Salesforce!
Formula used in the example:
AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)
2. Calculate Sales Commissions
Commissions can be tricky to calculate and undoubtedly Summary Formulas could never account for every use case. However, simpler commission schedules can be easily calculated with Summary Formulas.
Use Case: You need to calculate commission based on Amount on a rate that increases at set thresholds.
Formulas used in the example:
Commission Rate:
IF(AND(AMOUNT:SUM >= 10000000, AMOUNT:SUM < 15000000), 0.0005,
IF(AND(AMOUNT:SUM >= 15000000, AMOUNT:SUM < 20000000), 0.00075,
IF(AND(AMOUNT:SUM >= 20000000, AMOUNT:SUM < 25000000), 0.0012,
IF(AMOUNT:SUM >= 25000000, 0.0015,
0
))))
Commission:
IF(AND(AMOUNT:SUM >= 10000000, AMOUNT:SUM < 15000000), AMOUNT:SUM*0.0005,
IF(AND(AMOUNT:SUM >= 15000000, AMOUNT:SUM < 20000000), AMOUNT:SUM*0.00075,
IF(AND(AMOUNT:SUM >= 20000000, AMOUNT:SUM < 25000000), AMOUNT:SUM*0.0012,
IF(AMOUNT:SUM >= 25000000, AMOUNT:SUM*0.0015,
0
))))
3. Find Opportunity Win Rate with the Power of One
The “Power of One” is a concept made famous by Salesforce MVP Steve Molis who credits Thomas Tobin for its creation. The “Power of One” is simply creating a number formula field on each object with a formula value of “1.” Simple so far, right? But, why? This field now essentially will give each record a value of 1 in a number field; which means it can now be used as an accurate record count in Summary Formulas. Find a step-by-step guide in Steve’s Dreamforce presentation deck here.
Now that you have your Power of One, you can create an accurate Opportunity Win Rate Summary Formula!
Use Case: You want to find the Win Rate of each Sales Rep
A simple equation done right in your report!
Formulas used in the example:
WON:SUM/Opportunity.POO_Opp__c:SUM
I know what you’re thinking: “Couldn’t you just use RowCount instead of going through the trouble of creating a “Power of One” field?” In the above example, yes; however, the more complex the report the less reliable RowCount becomes, especially when working in reports with more than one object.
Summary Formulas give you the math you need done on your data right in your Salesforce report, saving you time and keeping track of your KPIs in real-time. If you are new to formulas or need some additional guidance getting started, you can check out Salesforce Help or the Formulas and Validations Trailhead.