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

When you’re ready to preview data, manually refresh the report preview. Or, see sample records returned after each edit by turning automatic previews on.

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 Select a Report Type menu, search for and select Opportunities.
  
• Click Start Report.
  

2. Click 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. Click Save.
   
6. Name the report Opportunities After Discount and change the Report Unique Name to OPPORTUNITIES_AFTER_DISCOUNT.
   
7. Click Save.
   
8. Next to Columns, click and select Add Summary Formula.
   
9. Name the column Amount After Total.
   
10. For Formula Output Type, select Currency.
    
11. In the formula field, paste IF(AMOUNT:SUM > 100000, AMOUNT:SUM*(0.95), AMOUNT:SUM).
    
12. To verify that the formula is valid, click Validate.
    
13. Select All Summary Levels.
    
14. Click Apply.
    
15. In the Amount column of the report, click , navigate to Summarize, and select Sum.
    
16. Disable the toggles for Row Counts and Detail Rows.
    
17. Click Save & Run.
    

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

Resources

• Help: Evaluate Report Data with Summary Formulas