A Brief Note on PowerPivot and Custom PivotCharts

Guest Post by Colin Banfield [LinkedIn]

In the next few posts, I plan on demonstrating techniques for creating various types of custom PivotCharts that use PowerPivot data and DAX measures. Each custom chart will have one or more of the following characteristics:

• Non-native to Excel, but nonetheless useful for various types of analysis. The box-and-whisker plot that I created in this post is one example of a non-native chart.
• Dynamic in nature, allowing charts to change based on slicer selections. The dynamic charts that I created in this post is one such example.
• Provide additional context, such as reference lines or conditional formats.

Why this emphasis on Custom PivotCharts?

A chart (really a graph, since a chart can be a table or graph. I’m using the term in Excel’s context.) is the best way to analyze data – for detecting patterns, trends, outliers, correlations, making comparisons, and so on.  A custom chart, done correctly, can facilitate the analysis process immensely. There are several Excel sites that are dedicated to the topic of custom charting. However, these sites are primarily focused on building charts from static data in Excel. Excel has not traditionally provided tools for easily interacting with charts created from data in worksheet cells or tables (and the available tools are limited at best). Furthermore, very few posts on the Excel sites discuss charting using PivotTable data. There’s no surprise here – standard Excel PivotTables are exceedingly limited in the way you can organize data for custom charting. For instance, you can’t modify or override row or column filters, and calculated fields are very limited in scope – so much so that I rarely use them.

However, PowerPivot and DAX ameliorate many of the standard PivotTable limitations – to the point that in many cases, I actually find it easier to organize PivotTable measures for custom charting, compared to organizing data in worksheet ranges or tables. In addition, PivotTables containing DAX measures in combination with appropriate slicers, provide a level of interactivity that is not otherwise possible.

It’s not all nirvana though – you cannot create scatterplot or bubble PivotCharts. Therefore, an important class of analysis dealing with detecting correlations in data, cannot be done in PivotTables (though such analysis, and more, can be done with the Power View add-in that ships with Excel 2013).

To date, chart enthusiasts have been severely underserved by the paucity of PowerPivot posts dedicated to the topic of custom charts. Therefore, I will attempt to redress the balance. These custom charts should be relevant for years to come. I was shocked, saddened, and disappointed to learn that no new charts or chart elements were added to the standard chart palette in Excel 2013.