STEM

Creating a data-driven, dynamic cause-and-effect chart

In this post, I share my method of creating a chart to show how events impact another variable such as sales and prices in a data-driven, easy-to-read, and dynamic way. Think of it as a cause-and-effect visualization. Let’s look at an example scenario where this type of chart can be very useful. This is done in Excel without any special visualization add-on.

We have a fictional dataset of new Electric Vehicle (EV) sales recorded at some dealership over time.
On the same timeline, we also have notable, relevant events that could impact the sales.
The dataset is below.

The number of new EV sold are recorded over a few months, and some relevant events to EV industry, and therefore its sales, are also noted as they occurred.

The objective is to show the events as well as the new EV sales on the same chart aligned to the same timeline. The events are categorical values in the dataset, timeline is dates, and sales are numeric…so all these different data types must be consolidated and made to work in harmony in a single visualization. This can be done with a Line chart and a Clustered Column chart combined as a Combo chart in Excel. The key is to have the data arranged in a way that Excel is able to render the data properly. The data table shown above as-is needs some minor adjustment.

Because we want to show the events as vertical lines along the timelines, we need to add a numeric quantity to each categorical event. So, we arrange the dataset with a new column ‘Value’ for each Event (I added 0.5 to 0.8 to each alternate event in order to stagger them to avoid overlap. Set your values according to your chart dimensions). My new dataset looks like this:

This arrangement is good enough to draw a Combo chart made of a Line chart and a Clustered column chart as long as we put them in different axes. By placing a Line chart with markers in the primary axis and the Clustered column on the secondary axis, we get a pretty eloquent chart as shown below.

This can be further improved by fine-tuning the column width, color, label sizes, colors, chart dimensions, grids, etc. to make it look clean and readable. My finished chart looks like this:

This chart shows various events that might impact EV sales (e.g. gasoline price spikes and drops, installation of new charging stations, EV incentives, and cold winter) and how they impacted the new EV sales.

For example, we notice that when the gasoline prices increased the new EV sales increased. The installation of new charging stations and government EV incentives such as rebates and discounts continue to drive the sales upward. It stabilized around 230 until a brutal winter hit the region. Possibly due to factors such as reduced battery and range efficiency in the cold, along with the snow-driving conditions and charging times the sales dipped quite a bit and remained low possibly also due to seasonal factors.

Alternatively, I can also show the chart using a Step chart instead of the typical line chart. For that, however, the data needs to be set up differently. The new dataset for my Step chart looks like this:

With this data, I can create a Line chart which will essentially look like a Step chart (remember, Excel does not have a built-in Step chart type so we have to take this extra step to create one). The Step chart version looks like this:

For the first Line chart, I used data labels for the events from a specific column ‘Event’ instead of the default values in ‘Value’ column, which simply determine the heights of the columns depicting the event timeline and wouldn’t be useful to show on the chart. Instead, the Event column values show the event names. Similarly, for the Step chart, I used the same column values from the Event column and show as Callouts. Recall that EV sales lines are in primary axis, and events are in secondary axis. For a more detailed discussion on Step chart, see my post on Step chart here.

So, while this is not a fishbone chart, it communicates the causes and corresponding effects very nicely and much more dynamically and is more data-driven than a fishbone visualization. Dates can be changed, added, deleted, as can be the events in just the source data table and the chart(s) will update accordingly without having to modify anything else. The vertical lines in dark purple are the events (or causes if you wish), and the line charts (blue line with sales numbers) is the effect…both aligned perfectly with the timeline along the x-axis.

The data presented here are hypothetical and used for illustrative purposes only.

I hope you found this post helpful and interesting. Explore this site for more tips and articles. Be sure to also check out my Patreon site where you can find free downloads and optional fee-based code and documentation. Thanks for visiting!

Related:

Back To Top