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:
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:
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:
Again, in normal English (sort of):
You see what we’re doing here 😉 It’s like magic... but not real magic… Right, let’s move on.
The grey bar indicates the remaining until the target is reached. If the target is not yet reached, the grey color fills the donut graph up until it’s 50% full. To calculate it, we use this formula:
The =IFS contains multiple conditions.
This is going really well! We’re half way there. Next up..
Oh yes, the green bar. This means we did well. We reached our target. Nothing more, nothing less. So this formula is easy:
It basically says: when target is reached, just show the target. If not, leave empty.
By just showing the target in green we get to use the ‘dark green’ to demonstrate everything above the target.
When you see that dark green, you know you are rocking that KPI. It’s the feedback you have been working hard for. That’s why it’s not ‘just green’. No! It’s kick-ass-awesome dark-green. This is the formula:
In plain English:
In this formula, you see we capped the maximum of over target at 100%. This is so the donut graph remains in a 50/50 setup even when you’re for example 230% on target.
The trick in designing this donut graph is that it looks like half a donut until you reach or surpass the target. To make it look like half a donut, we need a dimension value to fill the other half. By giving this dimension value the same color as the background, it becomes invisible and we made it look like a half donut.
But, when we surpass the target, we need this half to fill up with dark green. So the invisible half needs to start giving away space to the dark green. This formula does exactly that:
Again, in English:
In this formula, I also accounted for the maximum of 100% over target. When it does, it just values null.
That’s it for the Spreadsheet. We now have all the data we need. Let’s start creating the donut graph in Google Data Studio.
We’ve arrived at the fun part of this exercise. We are now going to use those boring metrics and transform them into a truly unique and beautiful graph. A donut graph that is. So open up your Data Studio report (or create a new one) and connect it to the Spreadsheet we just made.
Add a ‘Doughnut chart’ to your report. Select the right data source. Drag the ‘Dimension’ field onto the Dimension box. Drag the ‘Metric’ field onto the Metric box. Set the sorting to the ‘Dimension’ field and select Ascending. If you have multiple targets in the sheet, like the three examples in the demo Spreadsheet, you can create a filter to show only one specific target, in this case, it’s ‘Example 1’.
Next, we will make it look like I promised in the beginning. Follow these steps and you will feel like a true designer.
With the donut selected, navigate to the ‘Style’ tab. Set the donut width to your liking. Then open the menu ‘Manage dimension value colours’.
Here you will need to pick some colors for every dimension value. Below you will find the colors which I used, but feel free to use your own.
I added some rectangle shapes on the background to distinguish the different examples. Next, I added the metrics inside the donut for the exact reference. The easiest way to do this is connecting to the other sheet (‘Data’) in the Spreadsheet. The upper metric is ‘Actual’. The percentage below is a very simple formula:
If all went according to plan you could have ended up with something like the image below. I hope you found this article helpful and maybe even a little inspirational. Below you can find the links to the Spreadsheet and the Data Studio report.
Thanks for reading. If you have any questions, feel free to leave a comment. See ya!
