Create a Progress Bubble Chart in Tableau
Objective:
Our objective is to build a progress chart for different KPIs. In this example, we will be using: “Marketing Cost vs Budget”, “Operational Cost vs Budget” and “Headcount Cost vs Budget”. The Progress Chart will look like this:
Pre-requisites:
To create this type of chart, we need to have data in the specific format. Take a look at the design of the data in this format.
Data Summary:
Background Information:
As per the objective of building network chart, following fields are needed in a dataset:
- KPI: This field provides the KPI’s name like “Marketing Cost vs Budget”, “Operational Cost vs Budget” and “Headcount Cost vs Budget”
- Path: For each KPI, we have defined two values of Path i.e. 1 and 360. This field is required to build this type of chart
- Value: This is a numerical field showing a value of each KPI which is being shown in the Progress Chart later. The same value is being repeated corresponding to each path value
Steps:
Following are the steps:
- Connect to “Progress Chart.xlsx” data source. For reference, purpose data is attached along with this article
- Go to Sheet1:
- Create “Bins” using the field “Path”
- Now, before we start to build the chart, there are some calculations that need to be created. Create the following mentioned calculations:
- Index: calculation is- INDEX()
- PC_Value: This is used to show the value of each KPI. Calculation is- WINDOW_MAX(MAX([Value]))
- PC_PI: This calculation is used to build the circles of Progress Chart. Calculation is- WINDOW_MAX(MAX(PI()))
- PC_Depth: This calculation is required to show the remaining part of a circle which is left out from the given value. Calculation is- (1-[PC_value])*180
- PC_X: This is used as the X-axis for the chat. Calculation is- SIN([Index]*[PC_PI]/180)
- PC_Y: This is the conditional calculation required for Y-axis. Calculation is-
IF [Index]<[PC_depth] or [Index]> (360-[PC_depth]) THEN
COS([PC_depth]*[PC_PI]/180)
ELSE
COS([Index]*[PC_PI]/180)
END - PC_Y2: This calculation is used for dual Y-axis. Calculation is- COS([Index]*[PC_PI]/180)
- PC_Color: This calculation is used to define the color of each KPI. Calculation is:
- Index: calculation is- INDEX()
- Now, drag the “KPI”, “PC_Value” and “PC_X” fields to “Columns” shelf
- Convert “PC_Value” field to “Discrete”
- Drag “PC_Y” field to “Rows” shelf
- In the “Marks” card, change the type of chart from “Automatic” to “Polygon”
- Now, drag “Path (Bin)” to “Path” and “Detail” ‘marks card
- Change all the Table Calculations as compute using “Path (Bin)”
- Once, all the “Table Calculation” of all fields updated to “Path (Bin)”, the chart will look like this:
- Drag “PC_Color” field to “Color” marks card
- Now, drag “PC_Y2” and put it next to “PC_Y” in “Rows” shelf, to make the secondary axis
- Select “PC_Y2” and again change the “Table Calculation” as compute using “Path (Bin)”
- Remove the “PC_Color” field from “Color” Marks card of “PC_Y2”
- Remove the “PC_Color” field from “Color” Marks card of “PC_Y2”
- Select the “Synchronize Axis” option in the secondary “PC_Y2” axis
- Next step is to swap the position of “PC_Y” and “PC_Y2” in the rows shelf
- The last step is to hide the axis headers to make the chart visually more appealing
- This is how the “Progress Chart” looks like after the formatting changes