Funnel Charts Suck and You Shouldn’t Use Them

There are a lot wrong with funnel charts. And I am talking funnel charts in general – not just specific to Dynamics CRM.

Here are some of the issues I have with funnel charts:

1. The size of the sections do not correspond with the values
2. The “funnel analogy” is incorrect
3. You can’t easily compare the sections just by eyeballing them

In this example all four sections represent $25,000 USD. However, the blue “1-Qualify” area is about four times larger than the “4-Close” area. Another pet peeve is that you often see funnels used to chart data that is not related to phases or sales stages, which is the only area where it may make sense to use them.

In any case, a simple column chart is always much better representation of the data. If you want to read more about why funnel charts suck, this is the blog post for you. Peltier Tech – Bad Graphics Funnel Charts

But regardless of how much I preach funnel chart abstinence, I know you are all just going to go out and make them anyway. So let’s explore the options and see if we can put the “fun” in funnel.

Funnel Chart Custom Properties

Similar to pie charts, funnel charts only allow one series and one Y value per point. It also cannot be combined with any other chart type. So aside from label and legend tooltip editing, the only area we can play with are the custom properties.

The Custom Properties for funnel charts are:

• CalloutLineColor (Any color)
• Funnel3DDrawingStyle (CircularBase , SquareBase)
• Funnel3DRotationAngle (-10 to 10, 5)
• FunnelInsideLabelAlignment (Center, Top, Bottom)
• FunnelLabelStyle (Inside, Outside, OutsideInColumn, Disabled)
• FunnelMinPointHeight (0 to 100, 0)
• FunnelNeckHeight (0 to 100, 5)
• FunnelNeckWidth (0 to 100, 5)
• FunnelOutsideLabelPlacement (Right, Left)
• FunnelPointGap (0 to 100, 0)
• FunnelStyle (YIsHeight, YIsWidth)

Default values are in bold.

The custom properties are found in the series section of the chart xml.

Note that the format for custom properties is a little different from the standard properties. All the custom properties may not be in the chart xml when you first export it, so add the ones you need.

You can find the official funnel chart properties on MSDN here.

FunnelStyle

There are two main styles for the funnel chart. The FunnelStyle determines if the value is displayed in the height, or in the width. The following two charts are based on the same data to visualize the difference between the two styles.

FunnelStyle=YIsHeight

FunnelStyle=YIsWidth

YIsWidth does have some merit in that it addresses some of the standard funnel chart issues mentioned earlier. However, it does look odd that the first phase 1-Qualify is not drawn on the chart, although the value is present.

Funnel Neck

If your FunnelStyle choice is YIsHeight, then you can use FunnelNeckHeight or FunnelNeckWidth to adjust the funnel appearance.

FunnelNeckWidth=20, FunnelNeckHeight=20

With these you can adjust the rectangular area at the end of the funnel. The number you input is a percentage (0-100). The FunnelNeckWidth determines how wide the neck should be as a percentage of the chart. The FunnelNeckHeight determines how far up the funnel neck goes. I recommend increasing these percentages from the standard 5, to something more substantial like 20 as shown above. This way the last phase or two regains some of the lost surface area that I was complaining about in the beginning.

FunnelPointGap

A percentage that sets how much white space you have between the sections.

FunnelMinPointHeight

A percentage that sets the minimum height for a section. I recommend keeping this value at 0, its default, since increasing it will make the comparative sizes of the section even more obscure. An exception is if you have phases which usually contain relatively small values and you need to increase their visibility.

Label Position

The following custom properties control the label positioning.

• FunnelLabelStyle (Inside, Outside, OutsideInColumn, Disabled)
• FunnelInsideLabelAlignment (Center, Top, Bottom)
• FunnelOutsideLabelPlacement (Right, Left)

If using FunnelLabelStyle=Outside, then you can use the FunnelOutsideLabelPlacement to set the label either to the left or the right of the funnel.

When placing the label inside, use the FunnelInsideLabelAlignment to align it center, top or bottom.

3D Funnel

Adding 3D to a funnel does not make it better. But if you must, then use the 3D custom properties to set it as a circular base and always set the rotation angle to zero. That way you get a decent look on the chart, but with some 3D shading on the colors. The default is 5 on the rotation angle, so make sure you change it.

• Funnel3DDrawingStyle (CircularBase , SquareBase)
• Funnel3DRotationAngle (-10 to 10, 5)

If not, you might end up with this monstrosity.

Please just don’t!

Tips on Funnel Charts

Enough with the funnel chart slamming. Since it is a well-known chart type, it will be used and lots of users know exactly how to decode it when looking at it. It is even in the main default dashboard in Dynamics CRM. So here are some tips to quickly optimize a funnel chart.

Here are my suggestions:

• Use the same color increasing in strength per phase, as the chances for winning hopefully also increases.
• Label format to show values in thousands, increased the font and put them inside and centered.
• Increase the neck height and width to giver better area representation for the later phases.
• Added keywords for value, percentage and total to the legend tooltip.

Full sample xml of the above funnel chart is available at the end of this post.

Unfortunately, the keywords in Dynamics CRM charts do not support formatting. So, we cannot format 270000 as 270k in the tooltip. Or even put in thousand separators. If you have a minute, please go vote for that feature on CONNECT.

Here is a similar example with YIsWidth:

I don’t think I got this one to work properly. It is still only displaying three sections even though we have four phases, so the full chart xml sample is for the YIsHeight version. The changes mentioned are highlighted in blue. As usual, samples are provided for fun only. It should be possible to copy and paste the whole thing into notepad ++, save it as an xml file and then upload it to the opportunity entity in Dynamics CRM.

Thanks for reading. Please sign up for my newsletter and/or follow me on Twitter Follow @crmchartguy

<visualization>
  <visualizationid></visualizationid>
  <name>Putting the Fun in Funnel</name>
  <description>Shows the sum of estimated revenue in each stage of the sales pipeline.</description>
  <primaryentitytypecode>opportunity</primaryentitytypecode>
  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" count="10" aggregate="true">
          <entity name="opportunity">
            <attribute name="estimatedvalue" aggregate="sum" alias="sum_estimatedvalue" />
            <attribute name="stepname" groupby="true" alias="stepname" />
            <order alias="stepname" descending="false" />
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category>
          <measurecollection>
            <measure alias="sum_estimatedvalue" />
          </measurecollection>
        </category>
      </categorycollection>
    </datadefinition>
  </datadescription>
  <presentationdescription>
    <Chart Palette="None" PaletteCustomColors="90,91,151,213; 150,91,151,213; 200,91,151,213; 250,91,151,213;">
      <Series>
        <Series ShadowOffset="0" LegendToolTip="#LEGENDTEXT #VAL is #PERCENT of total #TOTAL" IsValueShownAsLabel="True" Font="{0}, 20px" LabelFormat="$#,#,k" LabelForeColor="59, 59, 59" ChartType="Funnel" 
                CustomProperties="FunnelStyle=YIsHeight, FunnelNeckHeight=20, FunnelNeckWidth=30, FunnelPointGap=1, FunnelMinPointHeight=0, FunnelLabelStyle=Inside, FunnelInsideLabelAlignment=Center">
          <SmartLabelStyle Enabled="True" />
        </Series>
      </Series>
      <ChartAreas>
        <ChartArea>
          <Area3DStyle Enable3D="false" />
        </ChartArea>
      </ChartAreas>
      <Legends>
        <Legend Alignment="Center" LegendStyle="Table" Docking="right" Font="{0}, 11px" ShadowColor="0, 0, 0, 0" ForeColor="59, 59, 59" />
      </Legends>
	  <Annotations>
		<TextAnnotation X="50" Y="0" Text="By CRM Chart Guy #crmchartguy" TextStyle="Default" Font="Verdana, 8px" ForeColor="Gray"/>
	  </Annotations>	  
      <Titles>
        <Title Alignment="TopLeft" DockingOffset="-3" Font="Segeo UI, 13px" ForeColor="0, 0, 0"></Title>
      </Titles>
    </Chart>
  </presentationdescription>
  <isdefault>false</isdefault>
</visualization>