In the Lightning Report Builder, Salesforce provides two different formula types: Summary Formulas and Row-Level Formulas. They sound similar, but have distinct differences between them! Formulas can be used in reports for grouping records, calculations, or to compare data within a record.
Summary Formulas go across multiple records, while Row-Level Formulas go across a single record. Summary Formulas can be thought of as the column-based formula where the formula would occur at the bottom of a column, whereas the Row-Level Formulas occur 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 a few different use cases and determine which formula type you would use.
Note: when writing this article, I assume that you are familiar with the Lightning Report Builder.
Scenario 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 click the SUM checkbox.
If you include a grouping based on Country, this will yield in a summary line in the report:
Scenario 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.
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 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!
This is a short and sweet example of a Summary Formula.
Scenario 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 from the Columns drop down, select ‘Add Row-Level Formula’:
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 then easily identify the misaligned States as well as the filter on the field:
There you have it, a simple Row-Level formula!
Summary: Some Considerations
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.