Do you miss using formula fields? Regardless of whether the answer is yes or no, reports come with some useful formula options:

• Column summaries
• Summary formulas
• Row-level formulas (beta, as of Winter 2020)

Using column summaries is the least complex way of creating a summary formula out of a numeric field. This is achieved by opening any numeric field options on the corresponding report's column header:

We can do one or all of the following operations:

• Sum
• Average
• Max
• Min

You can even click on the Columns sidebar to open up the Summarize popup:

In this example, we can see the subtotals and the summarized total:

This information is also available on the report's header:

Let's go a step further. With summary formulas, we can evaluate a report's group subtotals and grand totals, such as their tax application or fixed margin calculation. This feature is available in both Lightning Experience and Salesforce Classic.

To create a new summary formula (in Lightning), click on the column's sidebar options and choose Add Summary Formula. The following popup will appear:

The editor is divided into different sections:

• The left-hand side shows the fields that were used on the report and a list of available functions (mostly logical and math functions)
• The header displays the details of the formula (such as the return type, only numeric values, and name and description)
• The body of the formula and the chance to display the result of the formula at all summary levels, on the grand total only, or in specific groups (click on the Display tab for these options)

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:

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.

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:

Now, you should be able to see the 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:

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