Skip to main content
Topics#Reports & DashboardsRebecca Wildenhain's Question
Rebecca Wildenhain asked in #Reports & Dashboards
Jun 29, 2015, 11:59 PM

Formula for Weighted Sales Price

I am looking to create a custom field on Opportunity Products object to obtain a weighted Sales Price for Products. From there, I'd like to use it in a Products w/ Opportunities report to show weighted values by FY, FQ, Month, etc. The problem is that I can't pull Probability into the formula when working in the Opportunity Products object as it's an Opportunity field, not Opportunity Products. Likewise, I can't pull in Sale Price when working in the Opportunity object. Is there a way to link these 2 objects so I can create a formula using these 2 fields in either the Opportunity Products or Opportunity object? 

 

I also attempted to just create the forumula in a report using the following: 

 

LINEITEM_UNIT_PRICE:SUM * PROBABILITY:SUM

 

The formula calculates correctly for all Subtotal values, but the problem is that for Grand Total values, it is adding up all Probabilities to calculate a Probability Sum and then multiplying that by the Sales Price Total. Thus, resulting in an incorrect weighted total. 

 

Any help is appreciated! 
4 answers
  1. Mayank Srivastava (eVerge Group)
    Jun 30, 2015, 1:01 AM
    Rebecca,

     

    It's easy to pull in fields from the Opportunity object into the Opportunity Products object. You need to use the Insert Field button on the top of the formula editor to traverse from Opportunity Product -> Opportunity-> Field

     

    Rebecca, It's easy to pull in fields from the Opportunity object into the Opportunity Products object.

     

    -----------------------------------------------------------------------------------------------------------

     

    User-added image

     

     
Write an answer...
0/9000