Apply a Discount with a Custom Summary Formula

Learn About Summary Formulas

Ursa Major Solar’s marketing manager, April Hansen, thinks that running a promotion for the company’s large accounts would do wonders for their customer satisfaction metrics. She proposes the idea to Gabriela Livingston, Ursa Major’s head of sales operations, and Gabriela gives her the thumbs up to apply a 5% discount on accounts with opportunities that exceed $100,000. The company’s COO is thrilled by April’s initiative, but he wants to see an adjusted total for the revenue the company’s opportunities will bring in after the discount.

Summary formulas let you create a column for your report that logically and mathematically evaluates report data, so you can customize that data to meet your needs. To add a custom summary formula, group your report data, write the formula, then sit back and let the Lightning Report Builder handle the rest.

Analyze Report Data with Summary Formulas

First, let’s help April write a custom summary formula to create a column on an opportunity report that shows which opportunities would receive the discount, and the difference the discount will make on Ursa Major’s earnings:

  1. Create an opportunity report.
    • Click Reports.
    • Click New Report.
    • From the Choose Report Type menu, search for and select Opportunities.
    • Click Continue.
  2. Click Filter icon Filters.
  3. Click the Close Date filter, set the Range to All Time, and click Apply.
  4. Group the report by Opportunity ID.
    • To open the outline pane, click Outline.
    • Under Group Rows, click Add group, then search for and select Opportunity ID.
  5. Under Columns, click Down arrow and select Add Summary Formula.
  6. Name the column Amount After Total.
  7. For Formula Output Type, select Currency.
  8. In the formula field, paste IF(AMOUNT:SUM > 100000, AMOUNT:SUM*(0.95), AMOUNT:SUM).
  9. To verify that the formula is valid, click Validate.
  10. Click Display and select All Summary Levels.
  11. Click Apply.
  12. In the Amount column of the report, click Down arrow, navigate to Summarize, and select Sum.
  13. Disable the toggles for Row Count and Detail Rows. Report toggles
  14. Click Save & Run.
  15. Name the report Opportunities After Discount and change the Report Unique Name to OPPORTUNITIES_AFTER_DISCOUNT.
  16. Click Save.

Now the report shows updated figures to account for discounted opportunities.