Posts Tagged aggregate total on chart

Crowdsourcing the ultimate Sales Dashboard for Dynamics 365/CRM

When setting up a new Dynamics 365/CRM trial a few standard dashboards are included. However, I feel they lack something, and those who follow me on Twitter know I occasionally like to make fun of particularly the funnel chart. So here’s a hopefully improved version, that you can use, either in your organization or simply to spice up a demo environment.

Well, why not do something about it then. Plus, I’ve toying with this idea for a while anyway.

Crowdsourcing?

I did have the audacity to use the term “ultimate” in the title and the dashboard I provided is clearly not. However, with lots of input, we can make this much better.

Therefore; I would love to hear from you if you have suggestions how to make these dashboards better.

Suggestions could include, but are certainly not limited to:

  • Ideas for new KPIs
  • Ideas for new Charts
  • Ideas to improve formatting and make the charts easier to decode
  • Using charts contextually on record forms
  • Better use of colors between the different charts

My only request is that you elaborate on your suggestion, and I will try to incorporate as much as possible and hopefully have this grow into something very useful.

At the bottom of this post, I’ve included a zip file containing a solution with all the components for the dashboards in this post. The zip file contains both a managed and unmanaged solution that can be imported to Dynamics 365/CRM, and all the chart xml files individually.

Assumptions

It should be possible to import the charts on Dynamics 365/CRM organizations, so I will try to adhere to the following rules and assumptions:

  • Only use the charts within Dynamics 365/CRM*
  • Only use system fields
  • Only use system relationships
  • Avoid using calculated fields for KPIs – unless it is a system field
  • Take as much advantage of the existing setup as possible

*Why am I limiting myself to only using the charts within Dynamics 365/CRM and not including Power BI and Customer Insights. While Power BI and Customer Insights are powerful features, they are both tools external to Dynamics 365/CRM. You cannot as easily, share the charts and KPIs within CRM itself. Charts also have some significant advantages too. They can be contextual to the user logged in, respects the security roles, and they are real-time. “I just won an opportunity. I’ll happily wait an hour and then look at the sales leaderboard to see my position.” said no sales person ever!

 

The Sales Dashboard

Main sales dashboard with the most important KPIs for Dynamics 365/CRM

The Sales Dashboard consists of the following charts:

1. Sales Funnel

Somehow it is not a sales dashboard if there isn’t a funnel on it. It is the universal indicator of a sales process regardless of how wrong it may be. I have done my best to overcome some of those areas.

2. Sales Leaderboard

The Sales Leaderboard is also a standard component on a sales dashboard. This one includes both won and in progress revenue. Users are ranked per the amount won. Current user is emphasized in a stronger color so it is easier to see your own position.

3. Sales KPIs about the open opportunities

KPIs include est. revenue, average value, number of opportunties in the pipeline, next est. close date and date the last opportunity was created. On the dashboard, the user can flip views between “Open Opportunities” vs. “My Open Opportunities” to get both group and personal KPIs.

4. Phase Loss Rate on Est. Revenue

This chart gives you the percentage of revenue lost per phase in the sales pipeline. For example, in the qualify stage, if opportunities est. 100 million entered the qualify stage, but only 45 million of est. revenue made it to the develop phase, then the loss rate for qualify is 65%. The same calculation for the develop phase, but of course excluding all the opportunities that never made it that far. In an ideal world, you would lose more opportunities in the initial stages and have a much higher win percentage towards the end.

Additional charts with variations on how the calculations are made are included in the zip file, including using the number of opportunities rather than the est. revenue.

5. Sales KPIs for Won Opportunities

Like the other KPI chart, but this one focusing on KPIs for the opportunities that were won.

Competitor Strength

Scroll down on the dashboard and you get to the Competitor Strength chart.

Competitor Strength for Open Opportunities in Dynamics 365/CRM

The competitor strength chart lists all your competitors, ranked by how many opportunities you have lost to them.

On top of the lost bar, you can see how many opportunities are currently in progress.

Finally, in another bar, you have the est. revenue of the open opportunities where this competitor is a threat.

Sales Pipeline Analysis

Sales Pipeline Analysis in six charts in Dynamics 365/CRM

As mentioned earlier, I have included all the pipeline analysis charts in one dashboard for review. They all work in the same manner, although the calculations have a different focus as described by their names.

Download

As always, samples are provided for fun and training purposes only. Test properly prior to using in a production environment.

Sales Dashboard by CRM Chart Guy Version 0.0.0.1

Update Notes November 20, 2016.

Pipeline Charts now only uses the beginning number of the step name, so as long as your phases are 1 through 4, then you would not need to make changes. Won opportunities will also be filtered correctly and do not necessarily need to be moved to the close stage. Regardless of the stage on a won opportunity, it is assumed that it has made it through all the stages.

Added some of the pipeline charts to a separate User form to analyze an individual’s performance against how the company KPIs.

Zip file includes all the chart xml files individually and a managed and unmanaged version of the solution file for Dynamics 365.

Solution files for pre-Dynamics 365 environments are no longer included. However, all the chart xml files can be imported separately into previous versions. Presumably, all the way back to CRM2011 although I have not been able to test that.

Added a chart for the Opportunity Products to see what products are in the pipeline. Products less than 10% of the total are grouped into “Other”.

Original Solutions Notes

All the charts that use stages or the pipeline rely on the Opportunity field called Pipeline Phase. Most trial instances have data in this already. Otherwise, it is populated either manually or by a workflow. The chart also assumes that the naming of the phases is kept from the trial so they are called 1-Qualify, 2-Develop etc.

The assumption I made in the calculations is that won opportunities are in the 4-Close phase. Therefore, make sure that the process that updates the pipeline phase puts won opportunities 4-Close. They really should be in that stage anyway if won.

The solution files with Dyn365 in the name are specifically for new trials or environments that have already been upgraded. This solution also includes the Competitor Chart.

Solution files with CRM_8.0 in the name are for CRM2016 and earlier. This version does not include the competitor strength chart as there’s a bug in CRM that does not allow the distinct=true property to be imported in a solution file. This was fixed with Dynamics 365.

All the chart xml files are in the zip file as well, so you can import them separately, make modifications etc.

Thanks for reading. Hope you enjoy and please share and let me know of any good suggestions.

And don’t forget to sign up for my newsletter on this page and follow me on Twitter

, , , , , , , ,

14 Comments

Aggregate Total on top of Stacked Column Charts or Bar Charts in MS CRM 2011

Adding an aggregated total to the top of a stacked column chart sounds simple enough. It’s a couple of clicks in Excel. In MS CRM 2011 however, it is a different story. You would have to add an additional series to the chart, which then calculates and displays the total. But Stacked Column charts in MS CRM 2011 do not support multiple series.

CRM 2011 Stacked Column Chart with aggregate total on top

But that does not mean it is not possible. This CRM chart show us the aggregated total value of each of our pipelines, stacked by the pipeline phases, for each of the types of purchase processes.

So how did I create this chart? First, I need a chart with a series for each of the 4 pipeline stages, and one for the total. Then I display the series in the chart so they look like a standard stacked column chart. The total on top is a point chart type. The drawback here, is of course that all the possible series needs to be known so we can include them in the chart. In this case, all the series are each phase in the pipeline.

Since the user interface in MS CRM 2011 for creating charts does not support multiple series for stacked column charts, we need to create something else as our chart XML base and work off that.

I will use the UI to create a normal column chart and add all the series I need.

In this case a column chart with 5 series.

Chart Designer with all the series and category - CRM 2011 CRM Chart

1 for each of the 4 stages in the pipeline (Develop, Qualify, Propose, Close) plus 1 extra series for the total.

I want to see how my pipelines looks between the different types of Purchases Processes, so I’ll pick “Purchase Process” as the category so I get that on the X axis.

All the series I have added as a standard column chart.

Now I’ll export the chart xml and get to work. (Full chart XML samples are available at the bottom of this post)

CRM 2011 might add some references to a secondary Y axis in your XML. If that is the case, remove these first.

Rename the series in the Chart XML

I’ll start out by renaming all the aliases to something more sensible and easier to work with.

Original XML

Original Chart XML datadescription with aliases highlighted

Chart XML with renamed aliases

Chart XML with new aliases

Now it’s much easier for me to see which alias represents what data. I have “Purchase Process” as the category, the name for each pipeline phase, and the aggregated total at the end.

Change the Series Chart Types

Next I’ll change the first 4 series for the pipeline phases from ChartType=”Column” to ChartType=”StackedColumn”.

The last series for the aggregated total will need the following changes;

  • ChartType=”Point”
  • IsVisibleInLegend=”False” – don’t need to see “Total” in the legend
  • IsValueShownAsLabel=”True” – YES, this is the whole point of this chart
  • LabelFormat=”#,#,#” – no need to see decimals on the total
  • MarkerColor & MarkerBorderColor=”Transparent” – we just want the total amount – no need to see a marker also
  • The font I increased to 14px so it stands out more

Changing the series charttypes in the xml

Let’s do a quick import of the chart xml just to check what it now looks like in CRM.

Chart after intial modifications - no filters, all totals

We can see the total amount in the larger font, but the different pipeline phases are also showing the aggregate amount.

Let’s add a filter to each of the series in the fetchcollection, so they only show the correct amount for each phase.

Filter the series for the Pipeline Phases

Here’s the original fetchcollection.

Chart XML Fetchcollection with new aliases

I’ll add a filter to each of the series by creating a self-referential <link-entity>. This will allow me to make sure each series only sum up the value of the Opportunities, specific to its phase.  The easiest way to get the filter properties is by creating them in Advanced Find and export the FetchXML.

Each attribute will get a <link-entity> and <filter> added as follows.


<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
  <attribute alias="qualify" name="estimatedvalue" aggregate="sum" />
    <filter>
      <condition attribute="stepname" operator="eq" value="1-Qualify" />
    </filter>
</link-entity>

Here’s a part of the fetchcollection with filters added for each attribute for the pipeline phases. The last attribute I’ll leave without a filter as we do want the total on that one.

Chart XML Fetchcollection with Filters for each phase matcing the alias

Time to import the XML and have a look.

Chart with Total on Top - Filtered, but with Orignial colors and settings

Chart is now filtered and stacked properly for each series and there’s an aggregated total on top.

Adjust Chart Axis, Legend and Colors

It works, but we can make the chart look a lot easier to read and understand.

I’ll make the following changes:

  • IsValueShownAsLabel=”false” on all series except the total.
  • Add LegendText to all the series except the total
  • Add Color to each series – I’m going with increasingly darker shades of blue in this case
  • Add Format=”$#,#,k” to the LabelStyle for a shorter label on the Y axis

Last, I’ll reverse the series in the legend, because MS CRM 2011 automatically reverses it on stackedcolumn charts. In other words, I reverse the reversed order so it reads 1-2-3-4 instead of 4-3-2-1.

Cleaning up the Chart by adding colors ot the series etc.

One final import of the CRM chart XML and here’s the result.

CRM 2011 Chart - Stacked Column with Aggregate Total on Top

A couple of notes:

  • Bonus: Even though no Opportunities are in phase “4-Close”  we still see it in the legend
  • Filtering in the XML can be tricky – it’s your job to make sure everything relevant get’s accounted for
  • This approach allow you to add colors directly in the Series, which can be a lot easier than using the PaletteCustomColors
  • The Series have to be listed in the exact same order in the presentationdescription as they do in the measurecollection
  • The User can still hover the mouse over the chart to get the exact value of each pipeline phase in the tooltip

How about an Aggregate Total on top of a Stacked Bar Chart?

The same approach can be used for bar charts. All the chart types need to be a Stacked Bar for the pipeline phases and then a regular Bar type for the total, which then have to be made transparent. (XML sample included at the bottom of post)

CRM Chart 2011 Stacked Bar with Aggregate Total on top

Hope you enjoyed this post.

If you did, please follow me on Twitter for CRM Chart updates

Chart XML Samples

These chart xml samples are for reference only and not intended for use in a live environment without thorough testing.

They were created on a Microsoft Dynamics CRM 2011 trial with only the sample data added. The only addition I made to the sample data, was adding values to the Purchase Process field on the Opportunities.

Stacked Column Chart with aggregated total

<visualization>
<visualizationid>{C618533F-3784-E211-8B16-78E3B5114607}</visualizationid>
<name>Aggregated Total on top of StackColumn chart</name>
<primaryentitytypecode>opportunity</primaryentitytypecode>
<datadescription>
<datadefinition>
<fetchcollection>
<fetch mapping="logical" aggregate="true">
<entity name="opportunity">
<attribute groupby="true" alias="purchase_process" name="purchaseprocess" />
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="qualify" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="1-Qualify" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="develop" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="2-Develop" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="propose" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="3-Propose" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="close" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="4-Close" />
</filter>
</link-entity>
<attribute alias="aggregated_total" name="estimatedvalue" aggregate="sum" />
</entity>
</fetch>
</fetchcollection>
<categorycollection>
<category alias="purchase_process">
<measurecollection>
<measure alias="qualify" />
</measurecollection>
<measurecollection>
<measure alias="develop" />
</measurecollection>
<measurecollection>
<measure alias="propose" />
</measurecollection>
<measurecollection>
<measure alias="close" />
</measurecollection>
<measurecollection>
<measure alias="aggregated_total" />
</measurecollection>
</category>
</categorycollection>
</datadefinition>
</datadescription>
<presentationdescription>
<Chart Palette="None" PaletteCustomColors="55,118,193; 197,56,52; 149,189,66; 117,82,160; 49,171,204; 255,136,35; 97,142,206; 209,98,96; 168,203,104; 142,116,178; 93,186,215; 255,155,83">
<Series>
<Series ChartType="Stackedcolumn" LegendText="1-Qualify" Color="LightSteelBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedcolumn" LegendText="2-Develop" Color="CornflowerBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedcolumn" LegendText="3-Propose" Color="RoyalBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedcolumn" LegendText="4-Close" Color="DarkBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Point" IsVisibleInLegend="False" IsValueShownAsLabel="True" LabelFormat="$#,#,#" MarkerBorderColor="Transparent" MarkerColor="Transparent" Font="{0}, 14px" LabelForeColor="59, 59, 59"></Series>
</Series>
<ChartAreas>
<ChartArea BorderColor="White" BorderDashStyle="Solid">
<AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
<MajorGrid LineColor="239, 242, 246" />
<MajorTickMark LineColor="165, 172, 181" />
<LabelStyle Font="{0}, 10.5px" Format="$#,#,k" ForeColor="59, 59, 59" />
</AxisY>
<AxisX LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
<MajorTickMark LineColor="165, 172, 181" />
<MajorGrid LineColor="Transparent" />
<LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
</AxisX>
</ChartArea>
</ChartAreas>
<Titles>
<Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59"></Title>
</Titles>
<Legends>
<Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" LegendItemOrder="ReversedSeriesOrder" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />
</Legends>
</Chart>
</presentationdescription>
<isdefault>false</isdefault>
</visualization>

Stacked Bar Chart with aggregated total

 <visualization>
<visualizationid>{C618533F-3784-E211-8B16-78E3B5114607}</visualizationid>
<name>Aggregated Total on top of Stackedbar Chart</name>
<primaryentitytypecode>opportunity</primaryentitytypecode>
<datadescription>
<datadefinition>
<fetchcollection>
<fetch mapping="logical" aggregate="true">
<entity name="opportunity">
<attribute groupby="true" alias="purchase_process" name="purchaseprocess" />
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="qualify" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="1-Qualify" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="develop" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="2-Develop" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="propose" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="3-Propose" />
</filter>
</link-entity>
<link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
<attribute alias="close" name="estimatedvalue" aggregate="sum" />
<filter>
<condition attribute="stepname" operator="eq" value="4-Close" />
</filter>
</link-entity>
<attribute alias="aggregated_total" name="estimatedvalue" aggregate="sum" />
</entity>
</fetch>
</fetchcollection>
<categorycollection>
<category alias="purchase_process">
<measurecollection>
<measure alias="qualify" />
</measurecollection>
<measurecollection>
<measure alias="develop" />
</measurecollection>
<measurecollection>
<measure alias="propose" />
</measurecollection>
<measurecollection>
<measure alias="close" />
</measurecollection>
<measurecollection>
<measure alias="aggregated_total" />
</measurecollection>
</category>
</categorycollection>
</datadefinition>
</datadescription>
<presentationdescription>
<Chart Palette="None" PaletteCustomColors="55,118,193; 197,56,52; 149,189,66; 117,82,160; 49,171,204; 255,136,35; 97,142,206; 209,98,96; 168,203,104; 142,116,178; 93,186,215; 255,155,83">
<Series>
<Series ChartType="Stackedbar" LegendText="1-Qualify" Color="LightSteelBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedbar" LegendText="2-Develop" Color="CornflowerBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedbar" LegendText="3-Propose" Color="RoyalBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Stackedbar" LegendText="4-Close" Color="DarkBlue" IsValueShownAsLabel="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
<Series ChartType="Bar" IsVisibleInLegend="False" IsValueShownAsLabel="True" LabelFormat="$#,#,#" Color="Transparent" Font="{0}, 14px" LabelForeColor="59, 59, 59"></Series>
</Series>
<ChartAreas>
<ChartArea BorderColor="White" BorderDashStyle="Solid">
<AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
<MajorGrid LineColor="239, 242, 246" />
<MajorTickMark LineColor="165, 172, 181" />
<LabelStyle Font="{0}, 10.5px" Format="$#,#,k" ForeColor="59, 59, 59" />
</AxisY>
<AxisX LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
<MajorTickMark LineColor="165, 172, 181" />
<MajorGrid LineColor="Transparent" />
<LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
</AxisX>
</ChartArea>
</ChartAreas>
<Titles>
<Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59"></Title>
</Titles>
<Legends>
<Legend Alignment="Center" LegendStyle="Table" Docking="right" IsEquallySpacedItems="True" LegendItemOrder="ReversedSeriesOrder" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />
</Legends>
</Chart>
</presentationdescription>
<isdefault>false</isdefault>
</visualization>

, , , , , , ,

61 Comments