Finally, a field is referenced by its sum, average, max, and min values (we are not going to create a row-level formula that applies to any row of the report; instead we will focus on the summary parts of the report, that is, the total and subtotals).
Let’s say we want to get the average revenue per employee (given that the employee number is greater than 0; otherwise, we output -1), we can use the following formula:
IF(EMPLOYEES:AVG > 0, SALES:AVG / EMPLOYEES:AVG, -1)
This results in the following output:
Summary formula example
This way, we have acted on the report without creating a custom formula field on the account object. If your sales reps are smart enough, they can be trained to create their own reports.
The following are some things you need to take into consideration regarding summary formulas (if you want to go deeper, check out Salesforce Help at https://help.salesforce.com/articleView?id=reports_csf_tips.htm&type=5):
- Date and date-time fields are not supported.
- A summary formula cannot reference another summary formula.
- We cannot group/filter on summary formulas.
- We need at least one grouping to use summary formulas.
- We can get an #Error! result on a formula, for example, when dividing by zero.
Finally, let’s have a look at the latest addition to the reports formula (as of Winter 2020): row-level formulas.
Row-level formulas are available on Lightning Experience only and, as of Summer 2019, they are in their beta version.
To enable this feature, go to Setup | Feature Settings | Analytics | Reports and Dashboards Settings and click on the Enable Row-Level Formulas (Lightning Experience only) flag.
To create a new formula, use the Columns options menu, as shown in the following screenshot:
Creating a row-level formula
Now, you should be able to see the row-level formula editor:
Row-level formula editor
As we saw in the previous summary formula, we have a list of available fields and functions, main formula details, return types, and the body. This time, the return type supports the date, date-time, and text types.
We want to create a formula that counts the number of days it takes for an opportunity to be closed and, given the expected revenue, the average revenue per day (this is just an example and should not be taken as a real KPI for opportunities).
This formula can be written as follows:
‘€ ‘ + TEXT(ROUND(AMOUNT / (CLOSE_DATE – DATEVALUE(CREATED_DATE)),2) )
Here, Close_Date and Created_Date are evaluated together by subtracting one date from the other (we used the DATEVALUE function to convert the date-time of the Created_Date into a simple date value), which gives the difference in days between the dates. Then, the opportunity amount is divided by the number of days. We round the result to two decimal places, convert it into text, and append the Euro symbol to create something that pretends to be a currency field (at the time of writing, that is, Spring 2019, the currency output type isn’t supported).
This is the result:
Row-level formula example
Cool, right?
As of Summer 2019, we have severe limitations for row-level formulas (for the complete list, please refer to Salesforce Help at https://help.salesforce.com/articleView?id=reports_formulas_row_level_limits.htm&type=5), some of which are as follows:
- We can only have one row-level formula per report
- We can only reference up to three fields per formula
- Some field types are not supported (such as Boolean and picklist)
- We cannot use row-level formulas on joined reports
- The Edit (Salesforce Classic) option for editing a report is not available on any report containing a row-level formula