Evaluate Report Data with Summary Formulas

Use summary formulas to logically evaluate and do math with report results. This feature is new in Lightning Experience.

To see the total value of closed opportunities in a report, summarize the amount column. But what if you want to see total value after tax, and what if earnings are taxed at a progressive rate? (Say, 15% on sales below $1,000,000, and 20% on sales above $1,000,000.) Add a summary formula column named “Amount After Tax” to your report and write a formula like this one to evaluate the existing Amount column:

IF(AMOUNT:SUM < 1000000, AMOUNT:SUM * (1 - 0.15), AMOUNT:SUM * (1 - 0.20))

Now you can easily incorporate after-tax earnings into fiscal quarter planning.

Adding a summary formula

Before adding a summary formula to your report, be sure to group report data. Summary formula columns require at least one group.

  1. Edit or create a report.
  2. If necessary, group report data. From the Groups section, select a field from the Add group... lookup menu under GROUP ROWS.
  3. From the Columns section, click Add Column | Add Summary Formula Column.
  4. Optionally, to open the full editor, click Switch to Full Editor.
  5. Enter a summary formula. For example:
    IF(AMOUNT:SUM < 1000000, AMOUNT:SUM * (1 - 0.15), AMOUNT:SUM * (1 - 0.20))
  6. To see if your formula contains errors, click Check Syntax. Resolve any errors.
  7. Optionally, click FORMAT to choose how to format results, how many decimal points to show, and to change where the formula gets applied.
  8. Click Apply.

The summary formula appears as a new column in your report.

To edit a formula column, find it in the Formula Columns section and click its name.

To remove a summary formula column from a report, find the column in the report preview and click Add Column | Remove Column.

To delete a summary formula column, find it in the Formula Columns section and click X. Deleting a summary formula column is permanent. To get it back, you’ll have to recreate it from scratch.

As you prepare to add summary formula columns to your report, take note of these considerations:
  • You can’t group report data by summary formula columns.
  • You can’t filter report data by summary formula columns.
  • You can’t reorder summary formula columns.
  • Summary formula columns require at least one group.