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.