Give a chestnut! Tableau Tips (72): Use a bar chart or a ring chart to show the percentage of progress

Recently, many data fans have responded to the same demand: "Ada, I want to put a chart showing the percentage of progress on the dashboard, can you teach me to do it?"

There are two types of charts that we often use to present percentages: bar charts or circular charts (as shown in the figure below), which are achievable in Tableau.

So, how do you make a percentage bar chart and percentage circle chart in Tableau? Today we will learn the method.

In this issue of "Give a Chestnut", the Tableau technique that Ada wants to share with you is: use a bar chart or a ring chart to show the percentage of progress.

To facilitate learning, Lizi uses Tableau's own supermarket data source. After mastering the method, it is recommended that you try to use other types of data sources.

☞ Let’s take a look at how to make a percentage bar chart. The specific steps are as follows:

Step 1：Percentage bar chart

Suppose, I want to make a percentage bar chart to view: the proportion of orders in 2018 to all orders. (Tips: If you are not using the sample data that comes with Tableau Desktop 2018.1 and above, there may be no data for 2018 orders, please change the corresponding parameters according to the actual situation)

Step 2：Create calculated field

Create a calculated field "Number of orders in 2018": countd(if year([order date])=2018 then [order Id] end ).

Create the calculated field "Total Orders": countd([Order Id]).

Create a calculated field "% of orders in 2018": [Number of orders in 2018]/[Total orders].

Step 3：Create a chart

After the calculation field is created, we start to build the chart. First drag the "measurement value" in the "measure" into the "column", and then drag the "measure name" in the "dimension" into the "color" in the mark card, and then such a graphic will appear on your canvas :

Step 4：Remove other metric capsules

In the "Measurement Value" tag card, find the two fields we only need, "Orders in 2018" and "Total Orders", and remove all other metric capsules.

(Tips: Here is a little trick, you can press and hold CTRL on the keyboard and click the left mouse button to select multiple. After all selected, drag to remove, or click on any selected capsule drop-down box and select " Remove".)

Now, your interface should look like the following, with only the two items we need in the "Measurements" tab. At the same time, the graphics we need in the canvas are gradually clear, but there are still problems at present, we need to continue to make some modifications.

Step 5：Corrected calculation field

We know that for the percentage bar we want to present, the colored part should be a logically correct number, while the uncolored or gray part should be (total amount-logically correct number).

So we need to double-click the "Total Orders" capsule in the "Measurement Values" tab and modify it to "[Total Orders]-[2018 Orders]" (the reference field can be directly dragged in from the data pane) .

After modification, your interface looks like this. We can observe that the current bar graph is reasonable.

Step 6：Modify format

The next step is to modify the format. We double-click the legend color on the right to edit the color. We set these two bars to colors that conform to our normal logic. For example, the number of orders in 2018 is red and the minus number is gray.

After setting the color, the canvas looks like this. We found that red and gray were put in reverse.

If this happens, we can change the order of the graphs by dragging and dropping the order of the capsules in the "Measurement Value" card.

Step 7：Create a new worksheet

We change the name of this worksheet to "bar", and create another worksheet, named "percentage". Directly drag and drop the calculated field "% of orders in 2018" that we created in advance to the "Text" tag card.

Then modify the number format of the capsule in this field to "percentage";

Step 8：Put two worksheets into the container

After creating these two worksheets, we put them in a vertical container in a dashboard at the same time, and hide the title and axis.

Select the percentage worksheet and set it to "Entire View".

Then right-click the percentage area, set the format, and go to the left "Set Alignment Format".

Change the horizontal alignment of "default"-"zone" to "center".

In this way, our percentage bar chart is complete!

☞ Next, we will make another percentage ring chart.

Specific steps are as follows:

Step 1：Percentage ring chart

As with the percentage bar chart, here is an assumption. Suppose I want to make a percentage ring chart to see the percentage of sales in Guangdong Province to total sales.

Step 2：Create calculated field

Create the calculated field "Guangdong Province Sales": sum(if [province/autonomous region]=‘Guangdong’ then [sales] end ).

Create a calculated field "Proportion of Sales in Guangdong Province": [Guangdong Province Sales]/sum([Sales]).

Step 3：Create a chart

First of all, before performing any operation, first change the graphic type in the mark card to "pie chart";

Drag the "Measure Value" in the "Dimension" into the "Angle", and then drag the "Measure Name" in the "Measure" into the "Color", and then a graphic like this will appear on your canvas:

Step 4：Remove other metric capsules

In the same way as making a percentage bar chart, in the "Measurement Values" tag card, find the two fields that we only need, "Guangdong Province Sales" and "Sum (Sales)", and add the other measures All capsules are removed.

Now, your interface should look like the following, with only the two items we need in the "Measurements" tab. At the same time, the graphics we need in the canvas are gradually clear, but there are still problems at present, we need to continue to make some modifications.

Step 5：Corrected calculation field

Like the percentage bar chart, the percentage pie chart we want to present should be the logically correct number for the colored part, while the uncolored or grayed part should be (total amount-logically correct number). So we need to double-click the "Sum (Sales)" capsule in the "Measurement Values" tab and modify it to "SUM([Sales])-[Guangdong Province Sales]" (reference fields can be directly from the data pane Drag in).

After modification, your interface looks like this. We can observe that the current pie chart is reasonable and the angle of the pie chart has changed.

Step 6：Modify format

First change the page view to "whole view".

Then double-click the color block in the legend on the right to edit the color; we set these two colors to colors that conform to our cognitive laws, for example, the sales in Guangdong Province is blue, and the subtraction is gray;

Step 7：Make a ring chart

The next thing we have to do is to turn this pie chart into a ring chart, the method please learn: give a chestnut! Tableau technique (4): How to make hollow pie chart (ring chart).

Here, we quickly go through the key steps:

➀ Right-click and drag the "Number of Records" in "Measurement" to "Line", an option box will appear, we select "Minimum";

➁ Then, hold down the CTRL key of the keyboard, and drag the "minimum number of records" in the "line" to the right side to copy the same capsule;

➂ Right-click any capsule, select "Dual axis", and then select the "Minimum number of records (2)" tab in the mark card to edit the second copied pie chart. Remove all capsules ("measure name" and "measure value") in the mark card;

➃ Adjust the "size" of the pie chart circle to a smaller diameter;

➄ Then set the "color" of this ring chart circle to white;

Step 8：Drag the calculated field into the tab

Then drag the calculated field "Proportion of Sales in Guangdong Province" that we created in advance into the "Label" tab (note that the operations in the first few steps are for the circle of the "minimum number of records (2)" circle Edit, make sure it is still under this tab);

Right-click the capsule to change its number format to "percentage" style;

Right-click the axis on the left and uncheck "Show Title" to hide the axis;

In this way, our percentage ring chart is complete!

Chestnut summary：

Whether it is a percentage bar chart or a percentage ring chart, the idea of making these two graphs is to first create a logically correct value field with color and a gray (total amount-the logically correct value) minus metric value. Use these two parts to show the different colors of the two through the "metric name" to create a percentage bar chart and percentage ring chart.-Ada

Open up your Tableau and give it a try!