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.

**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