Add insight to your pipeline with Advanced Goal Charts

Update 4/14/17: Post updated to work with all versions, from Dynamics CRM 2011 to Dynamics 365.

The goal charts in Dynamics 365/CRM are already quite complex and hold a lot of information. The goal charts are combination of the Column and Point charts and display 4 different series (or Legend Entries). So why would I possibly want to make that even more complex. Well, some selling scenarios do require a more nuanced look at your pipeline.

First lets have a look at the built-in “Goal Progress (Money)” chart.
Goal chart in Dynamics 365, Dynamics CRM

  • Target – the blue bull’s eye shows what we are aiming for
  • Today’s Target – the triangle is a calculated measure of where we should be today
  • In-Progress is the current value of our pipeline
  • Actual is the value of Won Opportunities

Once you have gotten used to seeing that many legends on one chart, this chart is actually very good and easy to read. I quickly scan it and see if the green bar meets the triangle and if the grey bar is higher than the blue dot.

In business terms that would sound more like “Is my actual revenue for this sales period on track and do I have enough Opportunities of high enough value to meet my sales goal for the period”.

Where this chart is lacking information, is that the In-Progress series only show you the combined value of your pipeline. You actually have no idea if these are new Opportunities early in their development phase, or if contracts are almost ready to be signed. Maybe that’s why its the color grey.

“Weighted Revenue” (Est. Revenue * Percentage Probability of Winning) is often used to calculate how much you can reasonably expect to close. Whether or not your probability is based on a defined gated sales process, or an estimate entered by the User, is really up to what works best for your business. For this specific scenario I prefer the gated sales process as it not only tells you the likelihood of winning, but also more importantly, gives you an indication of how far in the sales process you actually are.

You can read more about sales processes and weighted revenue on Richard Knudson’s very fine www.dynamicscrmtrickbag.com. I won’t go into much detail here on exactly how to add Weighted Revenue to an Opportunity using Processes. Since the introduction of calculated fields in Dynamics 365/CRM it has also become significantly easier to create a field like Weighted Revenue.

Back to the chart. It would be fairly easy to just change the goal so it rolls up weighted revenue instead of the Est. Revenue. However, that would only give us a view of how much our weighted revenue is. That might be equally as confusing as just having the whole pipeline. We also want to add the rest of the pipeline so we know what our full pipeline value is.

Enter “Floating Revenue”

When calculating the Weighted Revenue, as a part of the process, make sure you also have a field called Floating Revenue where you insert the remaining amount (Est. Revenue – Weighted Revenue = Floating Revenue). In the Process you would add Est. Revenue to your new field and in the next step decrement it by the value in Weighted Revenue.

We also need to add the Floating Revenue as a Roll Up field for the Goal. Luckily, up to three Roll-Up fields on one Goal Metric is supported. I’d suggest using a new Goal Metric rather than to edit the existing one.

For our Goal Metric for the Advanced Goal Chart we have:

  • inprogressmoney – Weighted Revenue (originally for Est. Revenue)
  • actualmoney – Actual Revenue (same as the orginal Goal Metric)
  • customrollupfieldmoney – Floating Revenue (the new roll-up field)

Now that we have the data being rolled into the Goal, we need to modify the chart to display the extra data. Export the chart “Goal Progress (Money)” and use it as a base.
CRM Chart customization of the fetchcollection, also works for Dynamics 365
Notice the actualmoney and inprogressmoney fields. We will need to add the Floating Revenue to the list of attributes, the measurecollection, and the Series. We will get into that later. Note that the order here is relevant as we want to keep the stacked column chart with Actual Revenue on the bottom, then Weighted Revenue and finally topping up with the Floating Revenue.

Here are the additions to the xml file.

In the fetch collection I’ve added Floating Revenue – schema name on the Goal entity is customrollupfieldmoney.
Dynamics CRM, Dynamics 365 chart customization attribute

The new field also needs to be added in the measurecollection
Dynamics CRM chart xml customization, Dynamics 365 goal chart measurecollection

Finally we can add the extra Series. Since the Floating Revenue is fairly similar to Weighted Revenue from a charting perspective, we can just copy that series and make modifications to it.
Dynamics CRM chart xml customization, Dynamics 365 goal chart colors
In addition to copying the series I modified the color property for the both the Weighted and Floating Revenue. I gave Weighted Rev. the DarkGray color, and Floating Rev. the LightGray color. Using the color names make it easier to not get them mixed up, rather than modifying the RGB values.

Let’s import it back into Dynamics 365/CRM and see what happens. And remember to use the Advanced Goal Chart with the Advanced Goal Metric.
Dynamics CRM chart xml customization, Dynamics 365 goal chart with improved breakdown of weighted revenue.
So far, so good. The data is now being displayed as intended with the full pipeline broken into two, giving us an idea of where we are in the sales process for those Opportunities. However, there are still some items we can clean up before it’s really good.

First let’s fix the legend texts to something better than “Custom Rollup Field (Money)” etc.
Dynamics CRM chart xml customization, Dynamics 365 goal chart modifying the series in the goal chart
Adding the “LegendText=”XYZ” will allow you to modify the legends to something more sensible and utilize space better. In this case there’s really no reason to add the “(Money)” to the end of every description.

Dynamics CRM chart xml customization, Dynamics 365 goal chart updated advanced goal chart modified with xml

It’s getting better, but we can still do some improvements.

Let’s remove the “Name”. We do not need to display that the title of the Series is “Name”. In this case referring to the Goal Name. We can’t actually remove it, but we can make it transparent by changing the color.
Dynamics CRM chart xml customization, Dynamics 365 goal chart color of title adjusted in xml

The Y-axis can be improved a little by writing $100k instead of 100,000. That’s a lot of zeros to repeat on this chart.
Dynamics CRM chart xml customization, Dynamics 365 goal chart Format on axis adjusted
We do that by adding “Format” property and setting it to $#,#,k.

I would like to do the same for my Actual Revenue by adding the figure to the chart.
Dynamics CRM chart xml customization, Dynamics 365 goal chart adjusting the label format in series XML
I’ve changed the IsValueShowAsLabel to “True” and added the same format to the label.

NOTE: Here on the Series the property is called “LabelFormat”. On the Axis it is just “Format”.

Let’s have a look at how we are doing.
Dynamics CRM chart xml customization, Dynamics 365 goal chart with new label and improved axis

Starting to look like the finished product. Now this chart has one drawback that I want to fix. While I can easily hover my mouse over the grey parts of the stacked column to get the value, I still only get the value of either the Weighted or Floating revenue. The chart will not give me the full value of the pipeline. I can’t just look at the full height of the column either, as that includes the Won revenue. I would need to do some extra math here in order to get the full pipeline value. That is something I’d expect the chart to do for me.

To get the full pipeline value on the chart we need to add it as a field to the Goal Record. We can’t just add an extra rollup field as only 3 are allowed. However, all we need is a process that aggregates the Weighted and Floating Revenue to a new money field every time the Goal Record is recalculated.

I’ll add the new Series to the xml file in the same manner I added Floating Revenue to the fetch collection, measurecollection and Series.

For the Series I copied the “Target” series.
Dynamics CRM chart xml customization, Dynamics 365 goal chart updating the series XML chart
I added the IsValueShownAsLabel property, set the LabelFormat and increased the font to 12px. I also changed all the marker colors to “Transparent” so we do not see a Marker on the chart. Just the full value of the pipeline. Note that I added the IsVisibleInLegend=”False” as I do not want it to show up on the legend. I also removed the name of the series that I copied from.

Now, let’s have one more look at the chart.
Dynamics CRM chart xml customization, Dynamics 365 goal chart final goal chart by editing chart XML
Now we have a Goal Chart displaying the Won revenue and the pipeline divided into how much its weighted displayed against our goal and how far we should be today.

When reaching towards the end of a goal period, this chart makes it easy for Sales Managers to see how close their sales people are to reaching their goals. If your pipeline is mostly LightGrey, it means you are probably working with mostly new Opportunities making it unlikely that you will reach your goal. On the other hand, if the pipeline is mostly DarkGrey, you are far in your sales process and all the more likely to close within the goal’s period.

Note: The term “Floating Revenue” is something I invented for this chart. Not sure if there’s an official term for it, but “Floating Revenue” seems to make sense and works well opposite the term “Weighted Revenue”. “Floating Revenue”, you read it here first 🙂

Now that we have gotten this far, why not try and over-engineer this thing a little.

Since Weighted and Floating Revenue isn’t an exact science, we can add some approximation to how the chart is viewed. To do this, we will change the grading in the pipeline so it changes according how much of the revenue is weighted, without giving the exact amount.

Gradient colors xml Dynamics CRM chart xml customization, Dynamics 365 goal chart backgrading on series
Click to enlarge

We wil add a TopBottom grading on the colors and make sure the gradients meet each other, where the Floating Revenue turns into Weighted Revenue. In the example above I’m using the grey color (RGB 100,100,100) but I’m using it in the ARGB format so I can make the gradient go from very transparent to almost solid.

ARGB goes from 0, completely transparent, to 255 completely solid. More on that in this blog post.

For Floating Revenue I’ll start the transparency 20 and go to 120. For Weighted Revenue I’ll continue from 120 and go to 220.

In the example below, it should be clear which of the two goals is further in their sales process, and thus more likely to reach their target. The gradient also makes it clearer that the 475k is the value of the full pipeline.

Dynamics CRM chart xml customization, Dynamics 365 goal chart High chance of winning opportunitiesDynamics CRM chart xml customization, Dynamics 365 goal chart low chance of winning opportunities

The colors can be modified to make the transition more or less apparent depending on how much emphasis you want to give it. You could also make a 10 or 20 point difference in the color grading so you have subtle indicator as to exactly where Weighted and Floating Revenue meet. I could also remove either “Floating” or “Weighted” in the Legend and just have the In-Progress value.

Enjoy. Hope you liked the tip. Please sign up for my newsletter and follow me on Twitter 

25 thoughts on “Add insight to your pipeline with Advanced Goal Charts

  1. Hello!

    I have gone through a similar process to the above (wish I had found this post sooner), but alas there is one hold up I am now facing.

    Attempting to combine this solution with a enabled seems to fail at every turn. Thoughts or advice?

  2. Hey,

    I have followed your instructions on creating these chart improvements and it has been perfect for what I need to achieve. The only part that seems to be missing is the process for creating the new field in Goals for aggregating the Weighted and Floating Revenue every time the Goal Record is recalculated.

    How do I do this (not creating a field but having it aggregate the values)?

    Cheers
    Robert

    • Hi Robert
      Thanks for reading.

      You need an additional workflow that runs on the Goal Record every time the goal is rolled up. That workflow should aggregate weighted and floating to get the total. In a workflow you would have three steps.
      Set the field to Zero, then increment by Weighted, and finally increment by Floating Rev. Then you have the total.

      • Ok that’s great. Works perfectly.

        A new question – How can I get the chart to use a specific currency? It seems to only show in our base currency which is $AU. This is even for those that have their CRM currency preferences set to another currency?

      • Yes. For whatever reason the Goal record uses the base currency only.
        This actually is more of a workflow question.
        You need a workflow to calculate the value in your other currency, and then place those values in custom Decimal Number fields on the goal record. The workflow should run after every goal roll-up and you need a custom field for each of the values that you are using. Finally modify your current Goal chart to point to the custom fields instead.

  3. Awesome post, quick question though. Is there a way to code a value in for the graph for the “Goal” category, or do I need to make something within CRM that will hold that. I’m somewhat new to CRM, but I’m still dirnking from the fire hose of information, so any information would be helpful!

    • Thanks Bill. You can’t code in a value in the xml. In this case the category, x-axis, is grouped by the goal records. Each column is its own goal record. This post is using the standard goal functionality and charts with some features added.

  4. Great post! Question regarding the goal progress chart. Is there any way to show a different field for the group by? I’d like to use the Goal Owner instead of the Goal Name. Reason being, we’d like to have the goal names be consistent year over year (i.e. 2013 Meeting Goals – ” but that looks terrible in the chart. Instead I just want to show which is also the Goal Owner. Thanks, your site has been the best resource I’ve found for chart customizations in CRM!

    • Thanks Taylor.
      You should be able to add a groupby clause with the goalownerid to the xml. The groupby clause is generally not present in goal charts, but you can add one. However, you need to make sure that each user only have one goal in the View. Otherwise they will be aggregated.
      You can create a custom goal chart with the chart editor and use the Goal Owner for grouping. Then save it and export the xml. Then copy the groupby line from there to your regular goal chart.

      • In theory I understand exactly what you are saying however I keep getting an invalid XML file when making the updates to perform the group by. I attempted to add the following line to the fetchXML

        attribute groupby=”true” name=”goalownerid”

        Importing gives me the standard message that the xml doesn’t conform to the schema. If I remove the groupby attribute and just leave attribute name=”goalownerid” it will allow me to import. My guess is that you can’t have a groupby without aggregating the query. So I attempted to set aggregate=”true” and then put aggregates on each of the attributes, which also failed.

        Any other ideas? Thanks again!

  5. Excellent post – thanks for a great resource!

    Can the goal chart be adjusted to show layers for the pipeline phases that make up the in progress amount? Thanks in advance!

    Rick

    • Thanks Rick. The goal record can only roll up three different values, so that probably won’t be enough to show each phase in the pipeline. You could show them in a standard column chart, but then you would miss out on the date range, target and the percentage calculation that the goal record provides.

  6. Thank you so much for sharing your knowledge! I am working with someone who wants to see the Target on the Goal Chart as a horizontal line rather than a circle? Can you assist with making this happen?

    • Hi Juanita
      You can change the chart type from Point to Line. That’ll give you a horizontal line instead of circles. Remove the customproperties related to the Marker in the process. However, from there it may need some work depending on your setup to get the look that you want.

  7. Hi,
    I use 2 goals for sales people that have the same goal metric, but different roll up queries to measure actual money on opportunities based on line of business associated to the opportunity. I want to use this type of chart to display the actual, the target for each line of business for the week. Would I just use the actual(money) chart, add in the two series that I allows me to, then export to xlm and add in the 3rd series?

  8. Can the Goal Charts be done as cumulative? In other words, build each month upon the other. So the Target Goal for January, adds to February, then to March and so on, and the Actuals do the same and its represented in a line graph (actuals vs target). I am trying to find a way to do this and have not found a way to show it in a chart. Any help would be appreciated.

  9. Hi, I have two individual goals A and B and I need to show team goals(A&B) together by summing up their targets of goals A and B and percentage achieved. Is there a way to do this ?

  10. Hi, thanks for all your posts! Is there a way to remove a someone’s Goals from showing on the Chart without removing the goal from Dynamics? I just don’t want to display on Goal chart. Well, the client doesn’t. 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s