Overview

In this article we will show how to calculate weighted scores for a collection of records using North52 BPA. We will demonstrate this using Opportunities connected to an Account. When the estimated value or probability of closing change, we will recalculate the weighted scores for all open opportunities on that account and then update the Account's description field with the name of the highest rated opportunity and it's calculated score.

North52 Decision Suite

The North52 Decision Suite solution works like this:

• A formula will fire whenever the Est. Revenue or Probability fields change on an Opportunity 
• The formula will gather a list of all the Opportunities connected to that Account 
• A weighted score will be calculated for each Opportunity as follows: Est. Revenue * (probability / 100) 
• The list is then sorted by the weighted score in descending order
• The (now) first record in the list is selected as it has the highest weighted score

Setup Formula

• Navigate to Settings -> N52 Formula 
• Create a new formula, setting the following values in the Formula Guide:
  • Source Entity set to Opportunity 
  • Set Formula Type to Save - Perform Action
  • Select the Classic editor
  • Click Create 
  • Change the Name of the formula to Opportunity - Calculated Weighted Scores
  • Expand Source & Target 
  • Under Source Property, Ctrl-Click the Est. Revenue field and the Probability field
• Copy and paste the following formula into the Classic editor 

  ifTrue(ContainsData([opportunity.closeprobability], [opportunity.estimatedvalue]),
  
    xCacheLoadLocal('allOpps', FindRecordsFD('FindRelatedOpportunities', true, SetParams([opportunity.customerid]))),
  
    xCacheAddCalculatedFieldLocal('allOpps', 'weightedscore', 'double', 'estimatedvalue * (closeprobability/100)'),
  
    xCacheFilterLocal('allOpps', '', 'weightedscore DESC'), 
  
    SetVar('oppId', xCacheGetLocal('allOpps', 'opportunityid', '', 0)),
    SetVar('weightedResult', xCacheGetLocal('allOpps', 'weightedscore', '', 0)),
  
    UpdateRecord('account', 
      [opportunity.customerid],
      SetAttribute('description',
        FindValueQuickName('opportunity', GetVar('oppId')) + ' has the highest weighted value: ' + GetVar('weightedResult'))
    )
  )

• Click Save 

Add fetch-XML record to the Formula

• Click on the Fetch-XML tab on the right of the screen and then click on the plus(+) icon
• In the new window that opens set the Name to be FindRelatedOpportunities
• In the Query field paste in the following Fetch-XML:

  <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
    <entity name="opportunity">
      <attribute name="name" />
      <attribute name="customerid" />
      <attribute name="estimatedvalue" />
      <attribute name="statuscode" />
      <attribute name="opportunityid" />
      <attribute name="closeprobability" />
      <order attribute="name" descending="false" />
      <filter type="and">
        <condition attribute="customerid" operator="eq" value="{0}" />
        <condition attribute="statecode" operator="eq" value="0" />
        <condition attribute="closeprobability" operator="not-null" />
        <condition attribute="estimatedvalue" operator="not-null" />
      </filter>
    </entity>
  </fetch>

• Click Save
  

You are now ready to test the formula.

Testing

Create an Account with several Opportunities on it with varying values for Est. Revenue and Probability.

As you change the values, the description field on the account will update to state which is the Opportunity with the highest weighted score.