Summary Formulas vs. Row Level Formulas in Salesforce
Salesforce report formulas can be used in reports for grouping records, calculations, or to compare record data.
In the Salesforce Report Builder, you’ll find two different formula types: Summary Formulas and Row-Level Formulas. While they sound similar, there are distinct differences between them!
Summary Formulas go across multiple records, while Row-Level Formulas go across a single record. You can think of Summary Formulas as the column-based formula (where the formula result displays at the bottom of a column), whereas the Row-Level Formula result displays on the row itself.
|Summary Formulas||Go across multiple records.||Find it at the bottom of the column.||Example: Average amount of all renewal opportunities in a report.|
|Row-Level Formulas||Applies to a single record.||Find it on the row itself.||Example: Check if two fields on the same record have the same value.|
Let’s take a look at Row-Level and Summary Formula examples so you can determine which formula type you would use in your Salesforce reports.
Note: When writing this article, I assumed that you are familiar with the Lightning Report Builder – this Salesforce Report tutorial will get you up to speed.
Example 1: Total Opportunity Amount
Your boss asked if you can create a report that shows Opportunity Amount total broken down by Country.
To achieve this you actually wouldn’t need a Summary Formula field (although if that was your guess you were on the right track!).
- All you need is to click on the field and then click the SUM checkbox.
- If you include a grouping based on Country, this will yield in a summary line in the report:
Example 2: Average Monthly Case Volume for a Specific Product
You have been asked by the support team to generate a report that shows the monthly average of Cases for a specific Product in the last year.
In this case, you would need to use a Summary Formula.
- Before you can make the Summary Formula, make sure that you have the Product field as a ‘Group By Row’ field.
- Click on the drop-down next to Columns and select Summary Formula.
- To create the formula, we are going to take the Record Count (the total records for the whole year), divided by 12 (the number of months in a year):
- Don’t forget to give the column a name. In this scenario, the column name is “Monthly Avg Cases”. With that, you are all set to run the report!
Example 3: Checking Field Value Matches
You have been tasked with verifying that at the Case level the Billing State and the Shipping State of the Account for the Case matches.
With Row-Level formulas, you can now solve this instantly without creating a new field on the Account.
- Create your Case report and then select “Add Row-Level Formula” from the Columns drop-down:
- Once in the editor, we will create an IF statement that compares the Billing State to the Shipping State, resulting in either a 0 (for not matching) or a 1 (for matching):
Tip: Use the Fields and Functions menu on the left-hand side to ensure that you are pulling in the correct formatting.
- Once you are completely satisfied with your formula you can give the Column a name and hit Apply.
- In the report, you will be able to easily identify the misaligned States as well as the filter on the field. There you have it – a simple Row-Level formula!
There are some considerations to keep in mind when it comes to Row-Level formulas. For example, you can only have one Row-Level formula per report, and it can on reference a maximum of five fields. Also, you can’t use Row-Level formulas for cross-filters or buckets. To learn more about the restrictions check out the Salesforce documentation.
If you would like to learn more about Summary and Row-Level formulas make sure you check out the Trailhead modules on Row-Level and Summary Level formulas. Happy learning!
Handy post, thanks! Have saved for future reference
Can you use row level summary formulas in dashboard tables? I tried to the other day and got an error about aggregate formula fields? Has anyone else tried this and been successful?
That is a neat intro, where can I find more detail on this subject?
There are a couple of good resources from Salesforce:
I was able to create a row-level formula but when I try to create another one the option is grey out, is there a reason for that?
Because you’re limited at 1 row level formula per report.
“There are some restrictions when it comes to Row-Level formulas, including that you can only have one on a report at a time, and reference a maximum of 3 fields in the formula. To learn more about the restrictions check out the Salesforce documentation.”
Hello: I added a row-level formula field to show the number of days between two date fields. I grouped my report by one of the dates in the formula, and by default the formula field is showing a subtotal. Instead of a subtotal, how do I show the average number of days? Thank you!
Hi! Great material here thank you! I have a question, why in some custom report types the row-level formula is not available?
Dave James Phillips
Thanks very much for this Ben, great stuff! One key piece of info that helped me was the ‘only one row-level formula per report’. This was blocking me and now I know why.
Great post! Just wondering if you have any tips on Row Level formulas that compare field data so that it doesn’t distinguish between capitalization? I have a report where I’m identifying when the two fields do not contain the same data. The problem is it is flagging them as different if the capitalization is the only difference. My formula is IF(FieldA FieldB, “REVIEW”,””). Then I filter for only records with Review. But if FieldA is “John Smith” and FieldB is john smith, the row is tagged for review when they are really the same person.
Thoughts? Suggestions? THANKS!