Hello. There is a default field on an Opportunity record, Amount, which is a sum of the quantity multiplied by the sales price of each product added, so I imagine there must be a way to access these product fields from an Opportunity. I'm trying to create a weight average, but when I look at available fields when adding a new field in Opportunity, "Products>" isn't available in Insert Field. I see "Price Book>" but that doesn't have those fields available. Does anyone know how I would go about accessing those Product fields so I can create a weight average formula? Thanks.
#Forums Community #Formulas #List Field #Opportunity
Since OpportunityLineItem has a Master-Detail relationship with Opportunity, you can use Roll-Up Summary fields to pull in the needed values and a simple formula to calculate the weighted average.
Steps to Set It Up:
- Create Roll-Up Summary Fields on Opportunity:
- Total Quantity (Total_Quantity__c) → SUM of Quantity
- Total Revenue (Total_Revenue__c) → SUM of TotalPrice (which is Quantity × Sales Price)
- Create a Formula Field on Opportunity:
- Name: Weighted_Avg_Sales_Price__c
- Type: Currency
- Formula:
Total_Revenue__c / Total_Quantity__c
This calculates the weighted average sales price.
Why This Works?
- Roll-Up Summary fields can aggregate values from child records since OLI is Master-Detail.
- Formula fields can reference these roll-ups for calculations.
This should work perfectly, but let me know if you run into any issues!
- Create Roll-Up Summary Fields on Opportunity: