Dynamic TopN Reports via Slicers, Part 4 – The Dot Plot PivotChart

Guest post by Colin Banfield [LinkedIn]

After completing the Part 3 extension of Rob’s Dynamic TopN Reports via Slicers, Part 2 post, I did not plan on a forth installment. However, I did plan to write about creating dot plot PivotCharts sometime in the future. Later, it occurred to me that the TopN reports model provided the perfect foundation upon which to create dot plot charts.

The dot plot is not a standard chart type in Excel, so most users haven’t heard of, let alone used one. The dot plot is an alternative to a horizontal bar chart, and there are many situations where it is a better fit for analysis. For a good introduction to dot plots, see this excellent article by data visualization expert, Naomi Robbins.

The following summarizes some of the potential advantages of the dot plot over a bar chart:

  • When there are a lot of category items on a chart, a bar chart can look cluttered. Because a dot plot uses less “ink” to represent the same data, the resulting chart tends to be less cluttered.
  • The dot plot is often better than a stacked bar chart. The values in a stacked bar chart can be hard to compare because only the bottom bars have a common baseline.
  • Depending on how the dot plot chart is organized, you can gain better insights than using a clustered bar chart (see example in Naomi’s article).
  • Since the absolute length of a bar chart encodes its value, the value axis must start at zero. If the values in the chart are all a distance from zero, you can’t make good use of interval values on the value axis. On the other hand, the dot plot values are judged by position along the axis – length is not involved. Therefore, you can have more optimal intervals on the value axis.

    FIgure 1 shows dot plot PivotCharts based on the data used in Part 3.


    Figure 1 – Dot plot PivotCharts (Click figure for an expanded view)

  • Read the Rest

    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.…

    Read the Rest

    Today’s Post Delayed


    Today Post Delayed, NOT Cancelled, Due to Weather

    Yesterday I left Baltimore BWI airport at 11 am heading back to Cleveland.  Like basically all of the East Coast air travelers yesterday afternoon though, I never made it home.

    I spent the night in Queens, New York.

    So today’s post probably won’t be done until tomorrow, realistically.

    In the meantime, here were my attempts last night to make the best of the situation in my usual way – with off-kilter humor:

    Read the Rest