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.
Best answer by jean.nairon
View original