Visualizations

October 16, 2020

In this post I will explain how to create a target graph using a donut chart, pulling data from a Google Sheet, and add some design ideas. I will also share the Sheet and Data Studio report.

A while ago I shared a snapshot of a target visualization using a donut graph in Google Data Studio. I got a few replies asking how to create this, so here it is 🙂

I will explain how to create a target graph using a donut chart, pulling data from a Google Sheet, and add some design ideas. You can see the result in the image below. I will also share the Sheet and Data Studio report.

First, we will create the Google Sheet with the data, next we will set up some delicious donuts in Data Studio.

*Important note: this target graph can only handle up to 100% over the target (i.e. 200%). That’s because we divide the donut in half; 100% (until target) + 100% (over target). If it exceeds this, the donut graph will not break, but it will stop showing everything above +100%.* *The percentage inside the donut will still show the real deal.*

All the calculations necessary for the target graph to work will be done in the Sheet. Basically, you only need two metrics for this:

- Target metric
- Actual metric

In the Spreadsheet, I created a sheet called ‘Data’ and it looks like this:

A donut graph always contains 1 dimension and a corresponding metric. In order to create the donut, we will have to divide the above two metrics over multiple dimension values. For the target graph, we need the following dimension values:

- Red – When actual is below x% of the target;
- Orange – When actual is above x% of the target but still under target;
- Grey – The remaining until the target is met;
- Green – When the target is reached;
- Dark green – Everything over the target.

I created another sheet called ‘Donut graph’, containing two dimensions (‘Example’ and ‘Dimension’, pardon my creativity here 🙂 ) and one metric column. It looks like this:

The dimension values need to be numbered so we can lock the sorting in the donut graph later on.

Next up we will use formulas to generate the metrics per dimension value. For any other target graphs, in this case, Example 2 and 3, you can just repeat the process below.

The formulas I share below contain references that make sense in the Spreadsheet I made. You might have to update these references (e.g. “Data!B2” or “Data!C2”) in your own project. I will try to explain every formula in normal English so you can implement them on your own. That’s learning right 😉

To calculate if the actual metric is below 75% of the target, we use the following formula.

In English, that's:

I don’t know if that last part made any sense but I will try to explain it as easy as possible.

To calculate the metric when below target but equal or over 75% of target, we input the following formula: