Admin Office Hours - 8/10/17

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.

Office Hours are starting now! Click here to join!