Share

Complex Goal Calculations Using Dynamics CRM 2015 Calculated Fields

We have talked about goals many times on the Hitachi Solutions CRM Blog, because we think they are a great feature.

In the standard goal scenario, you have a Target, an Actual value, and an In Progress value. Target is your goal, Actual is the amount completed, and In Progress is the value of outstanding records that may close and become part of the Actual.

The Percentage Achieved field calculates progress toward your goal by dividing Actual by Target.

But lets say that rather that tracking Actual and In Progress, you wanted to track two different metrics, and compare the combined total to the Target to get a combined percentage to goal?

image


In this example, rather than showing Actual vs. In Progress, we want to see the value of two different types of opportunity together. However, when we look at the goal data, the percentage achieved will be calculated using only the “Actual” metric, so users won’t know what percentage they have acheived.

To solve this, I created a new field called “Weighted Percentage Achieved.” For Data Type I chose “Whole Number,” and for Field Type I chose “Calculated.”

image

In the calculated field options, I specified an action formula to set the value of the new custom field to actual + in progress/target. I multiplied by 100 to make it a whole number.

((actualmoney + inprogressmoney) / targetmoney) * 100

After adding the new field to my view and publishing, when users see the chart with the goal and hit the button to view the records that make up the chart, they now see a percent achieved value that makes sense for this goal. The standard Percent Achieved field is also preserved, so it can be used with normal goals.

I really like the calculated field formulas—anybody who has written formulas in Excel or other applications should be able to pick them up very quickly.

Free Guide
On-Premises vs. Cloud CRM

A guide to help you determine the best solutions for you.