Jun
27
2017

Sunburst Chart in Tableau

The sunburst chart is ideal for displaying hierarchical data. Each level of the hierarchy is represented by one ring or circle with the innermost circle represents top of the hierarchy. The circle in the center represents the root node, with the hierarchy moving outward from the center. A sunburst chart without any hierarchical data (one level of categories), looks similar to a doughnut chart.

 

 

 

 

 

 

 

 

 

 

 

 

 

Image Courtesy: http://www.datavizcatalogue.com

If you want to create a Sunburst chart in Tableau ,do a google search and it will definitely lead you to a blog post from Boran, who has figured out the calculations to perform in Tableau to get this working. We owe him a big thanks.

In order to re-create the sunburst chart, we need to format the data in a hierarchical fashion.Let’s imagine we have data as below. We’d need a way to calculate the hierarchy along with level. There are two ways two generate the Level. 

Sunburst_Data

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Note: Regarding Data Preparation

Create a data in such a way that Level 2 Sum up to Level 1 and Level 3 Sum Up to to Level 2 and so on.

For Example: 

Furniture = 2700 (600+900+1200)

Furniture Bedroom = 600 (400+50+150)

 

 

 

 

 

 

 

 

 

Method 1:

You can load the data in your SQL Server and generate the “Hierarchical Level”. Copy and paste the below SQL code. Below query help us in generating the hierarchy result.

SELECT DISTINCT t.Level, t.Item, t.Path, t.Sales FROM (

 SELECT 

 "Level" =

 CASE 

     WHEN [Product Class] IS NULL AND [Product Line] IS NULL AND [Product Type] IS NULL AND [Product Item] IS NULL THEN 1

     WHEN [Product Line] IS NULL AND [Product Type] IS NULL AND [Product Item] IS NULL THEN 2

     WHEN [Product Type] IS NULL AND [Product Item] IS NULL THEN 3

     WHEN [Product Item] IS NULL THEN 4

     ELSE 5

END   

 ,"Item" =

 CASE 

WHEN [Product Class] IS NULL AND [Product Line] IS NULL AND [Product Type] IS NULL AND [Product Item] IS NULL THEN [Product Family]

WHEN [Product Line] IS NULL AND [Product Type] IS NULL AND [Product Item] IS NULL THEN [Product Family] + ‘ > ‘ + [Product Class]

WHEN [Product Type] IS NULL AND [Product Item] IS NULL THEN [Product Family] + ‘ > ‘ + [Product Class] + ‘ > ‘ + [Product Line] 

WHEN [Product Item] IS NULL THEN [Product Family] + ‘ > ‘ + [Product Class] + ‘ > ‘ + [Product Line] + ‘ > ‘ + [Product Type]

ELSE [Product Family] + ‘ > ‘ + [Product Class] + ‘ > ‘ + [Product Line] + ‘ > ‘ + [Product Type] + ‘ > ‘ + [Product Item]

END 

,Path = 1

,SUM(SALES) as Sales

 FROM [demo].[tableau_sunburst]

GROUP BY GROUPING SETS ( ([Product Family], [Product Class], [Product Line], [Product Type], [Product Item]),

([Product Family], [Product Class], [Product Line], [Product Type]),

([Product Family], [Product Class], [Product Line]),

([Product Family], [Product Class]),

([Product Family]))

Notice that Path = 1. We'll union all this query, with the second query having Path =  203.

Read an article about Group by Grouping Sets:

http://dcx.sybase.com/1200/en/dbusage/ug-olap-s-51258141.html

Method 2:

We will be using Excel for generating the desired Format. Let’s copy the entire data (Ctrl +A+C) From sheet 1 .

Go to sheet 2 . Select Data from Excel toolbar -> Text to columns -> Delimited.

Assign a Proper column heading as per your choice. Now create a dummy column in your dataset and named as “Source”. This column helps us in finding the right Level for our sunburst chart.

Source Column:

Concatenate each string value with Delimiter > (available in each row) starting from Left to right. If there is no text in the cell, don’t add Delimiter > while concatenating. Let’s use a simple excel function and get this done. Drag this formula till the last row.

=IF(LEN(TRIM(A2))=0,"",A2)&IF(LEN(TRIM(B2))=0,"",">" &B2)&IF(LEN(TRIM(C2))=0,"",">" &C2)&IF(LEN(TRIM(D2))=0,"",">" &D2)&IF(LEN(TRIM(E2))=0,"",">" &E2)

 

Level Column:

Create another dummy column named as Level. We will again use simple excel function for counting the > separated values in a single cell. Drag this formula till the end and get the required level.

=LEN(TRIM(F2))-LEN(SUBSTITUTE(TRIM(F2),">",""))+1

You can also use another Excel function for generating the ““Hierarchical Level”. Make sure to delete the “Null” from each cell (wherever it presents) so that each cell will either be blank or empty.

=IF(NOT(ISBLANK(E2)),5,IF(NOT(ISBLANK(D2)),4,IF(NOT(ISBLANK(C2)),3,

IF(NOT(ISBLANK(B2)),2,1))))

Use any of the two Excel formulas for generating the required levels.

Step 1:

Connect a new data source, Excel > Sunburst_Data.xlsx by using the Legacy Connection.

Step 2:

You can either use SQL query or Excel formula for generating the level. As I have already generated the Level inside my excel File, so I will use the union the Path =1 with the second query having Path = 203

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Step 3:

Add a new sheet, and then convert Level and Path to Dimensions, if they are not already Dimensions.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Step 4:

Create the following calculated fields, in the order as listed in the table.You can directly copy the formulas from Bora’s workbook. I did my best to explain the logic behind the advanced Formula.

Sunburst Chart Calculation Definition

Step 5:

Right-click Path > Create > Bins. Set Bin size to 1 and name to Path (bin). Tableau Engine effectively generates a range of value from Min to Max even that value is not present in your data. Here, the size of Bin is 1, so it will generate a value from 1,2,3,4… till 203. We are simply using Data densification here.

 

 

 

 

 

 

 

 

 

 

 

 

Step 6:

It's important the following steps are performed in sequence. I have taken screenshots of each step so you can verify your actions.

Note: Add the entire dimensions in the Detail shelf. Put the entire dimension in the same order as if it listed in the columns from left to right. Remember the order as this is very important for Table calculation.

First change the default marks from “Automatic “to “Polygon”. Drag the entire dimension into to the Detail shelf and Path(Bin) into the  Path Marks. Arrange the order of the dimension on the detail shelf as it is appearing in our excel data sheet. Arranging the order of dimension in detail shelf  will help us in our Nested Table calculation.

 

Just for my own convenience I am keeping Source at the top of the order so that I can see the hierarchy. It’s completely your choice . This change will not affect anything.

So our order will be :

 

 

 

 

 

 

 

 

 

 

 

 

Step 7:

Add Path (bin) to the Rows shelf, then Right-Click and select Show Missing Values. Move Path (bin) from the Rows shelf to the Marks card. Add Max Level and Max Sales to the marks card as Detail. Then, set Compute Using to Path (bin).

Step 8:

Add X into the Columns and Y into the Rows. Then, right-click on X > Edit Table Calculation, and configure as shown in the screenshot below (remember the order of items matters).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now, we just need to select different Calculated Field which we created in step 8 and perform the nested table Calculation.

Index:

Index should be computed based on Path (Bin) only.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Slice Level:

Select everything except “level”.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Edges:

Select everything except “Path(bin)”

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Size of Slice:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Max Level and Max Sales  

We already added Table Calculation in Step 7. Both the Fields should be computed via Path (bin)

Step 10:

Perform the same table calculation for “Y” which is present in “Rows” Shelf.

Step 11:

Right click on the three dots (…) against the Product family and Product class, and change the Marks property from detail to color as shown below. Now your Sunburst chart is ready.

Be mindful to have a really solid use case for this visual before implementing it in production

 

About the Author: Rajeev Pandey

I'm Rajeev, a Tableau Lover, Data Evangelist from Hyderabad, India. I am a multidisciplinary designer working in data visualization, interaction design and innovation.Expertised in developing Tableau , Web focus based visualization and reporting applications.I am quick learner who can absorb new ideas and can communicate clearly and effectively.I love creativity and enjoy experimenting with various technologies.

Leave a comment

You must be logged into post a comment.