Do Not Build a Column Chart Like This – Please! - Excelerator BI

Do Not Build a Column Chart Like This – Please!

I was inspired into action this week by one of the worst visuals I have seen during the COVID pandemic. Here is the visual below, take a look and see what you think.

When I first saw this chart, I simply couldn’t make sense of it. The first thing I noticed were the colours. I simply couldn’t understand why there were 2 different colours for different sub-groups of age groups. Then I couldn’t understand why the death rate for 60 – 69 year old’s was so high. It literally took me 2 mins to work out what was going on. Once I worked it out, it was obvious what the report writer was trying to do, but it is simply a poor approach, poorly executed.

You can’t just go ahead and change the way that data visualisation is supposed to work and expect the audience to understand. The role of a report writer is to make the data easy to consume, not hard.

Granular Detail

Let’s take a step back. I have reproduced the root issue that the report author was trying to solve using my Case Fatality Rate in Australia COVID report. Take a look at the chart below.

The issue is that the tall columns are orders of magnitude larger than the short columns. This makes it hard to see the detail in the short columns. In my reproduced data/chart above, the difference is no where near as stark as with the bad visual at the top of this post, but it’s the best data I had at hand.

Compare the original poor quality chart with the visualisation I have produce below.

What do you think? Is this easier to understand without explanation?

There are a couple of tricks I used to produce this visualisation and you can see those in the video below, including:

  • Connect to online spreadsheets
  • Mapping tables to handle different levels of granularity without losing the detail
  • Adding manual data records to a table using a list of lists (there are other ways)
  • Creating a table from a list of lists
  • Preventing future issues with your data by removing duplicates when there are none.
  • Visually separating data on your report pages.
  • Rolling up/grouping data into summary groups
  • Creating sort columns in Power Query
  • Changing individual column colours in a column chart

This is part of my ongoing series of videos where I don’t just show you how to do it, once I worked out all the issues. I am trying to show you the reality of what happens when you try to build reporting solutions to share with others – warts and all. You will see in this video that I tried a few other metrics to reproduce the problem, but finally settled with the one above. This is not a real world problem, however. The reason I had this problem was I didn’t have the source data from the original chart so I tried to make the best of what I had. The first couple of choices illustrated the point, but ended up being meaningless for the ongoing continuous improvement of my COVID report. Regardless, the learning experience is still the same.

The video runs for an hour, but you can safely watch it at 1.5x or 2x and still get the learning.

1 thought on “Do Not Build a Column Chart Like This – Please!”

  1. I think that was a very good presentation, it covered so many, perhaps small, but important issue of the work everyone how publishes data should take in account:
    1. So many charts out there are BAD!
    2. It takes allot of small but important steps to get the right data the right way.
    3. Every step counts.
    4. Sometimes (like in this case) it is impossible to have all the data clear by importing metadata, data and building measures, and without manual work to decide where to filter, who and what do display, the result can’t be perfect.
    I liked it

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top