Creating a Renewal Rate % based on two fields from Opportunity Object

  • 1
  • Question
  • Updated 1 year ago
  • Answered
Archived and Closed

This conversation is no longer open for comments or replies and is no longer visible to community members. The community moderator provided the following reason for archiving: old announcement/article

We could use some assistance on a report we are trying to build. The end goal here is to build a report that shows the CSMs their “Renewal Rate %”. This is using a calculated field based on two fields (‘Amount Closed’ and ‘Available Amount’) from the Opportunity Object.

So, what we have tried so far, is to create a custom MDA object and then a new rule to push data from the Opportunity object to this new custom object. What we are having difficulties with is getting an accurate total renewal rate % for each CSM. We are able to Sum the ‘Amount Closed’ and ‘Available Amount’ but not take those Sums and create a ‘Renewal Rate %’ for each CSM (at the CSM level not each individual Account). We tried creating a rule that pushed the Sum of ‘Amount Closed’ and ‘Available Amount’ to the new custom MDA object, where we created a calculated field for the ‘Renewal Rate %’ using the data that was pushed from the rule. However, this does not sum it into one value, it gives us a value per opportunity. Which wouldn’t be an issue, since we can Sum in the Report Builder using the ‘By’ option set to the CSM (which does give us an accurate Sum of ‘Amount Closed’ and ‘Available Amount’). The issue with this method is that it also Sums the ‘Renewal Rate %’ which isn’t accurate, since it is just adding up the %’s from the calculated field in the MDA, with no weighting option for how much more a contract was renewed for. We also have tried to create a calculated field in the Rule we were building but that doesn’t give us the correct value either due to the formula options that are available, doesn’t include the option for ‘A/B’.


Is there a way to calculate the Renewal Rate % total per CSM? Based on those two fields which we are able to push into the MDA.
Photo of Manmeet Dosanjh

Manmeet Dosanjh, Champion

  • 3,932 Points 3k badge 2x thumb

Posted 1 year ago

  • 1
Photo of Denise Stokowski

Denise Stokowski, Official Rep

  • 12,290 Points 10k badge 2x thumb
Manmeet - Have you tried using Bionic Rules?  Since you basically need two steps - one is to aggregate by CSM and create your data set - then you can do in "an in row calculation" either as part of the Bionic Rule or directly in your target object (Target Object - CSM, Amount Closed, Available Amount, Renewal Rate).  You probably also want to do this by quarter.
Photo of Manmeet Dosanjh

Manmeet Dosanjh, Champion

  • 3,932 Points 3k badge 2x thumb
Thanks! I will give it a try with Bionic Rules. We have not used these here yet so I wasn't sure if they had the capabilities we needed. I will play around with bionic rules to see if it gets me to the correct Renewal Rate.
Photo of Sai Ram Pulluri

Sai Ram Pulluri, Official Rep

  • 12,334 Points 10k badge 2x thumb
Hi Manmeet,  I recently started going through all of the pending questions . Was checking to see if this has been resolved .
Photo of Manmeet Dosanjh

Manmeet Dosanjh, Champion

  • 3,932 Points 3k badge 2x thumb
Hi Sai,

Thanks for checking back. I was able to figure it out after some experimentation. All set here. Thanks!
Photo of Sai Ram Pulluri

Sai Ram Pulluri, Official Rep

  • 12,334 Points 10k badge 2x thumb
Great to hear that,you are always welcome for any questions, problems and Ideas.

This conversation is no longer open for comments or replies.