Drill Down Dendrogram Chart in Tableau

What is a Dendrogram Chart?

A dendrogram chart or a "Tree Map" is a diagram for visualizing the hierarchical relationship between data/objects making it easy to spot the pattern. It is most commonly used to create a tree formation showing hierarchical clustering of data in dashboard as shown below. Data densification techniques(e.g.: Sigmoid function) is used to show the curve and pattern in the diagram.

Let's take the sample superstore dataset to visualize the sub-category wise sales. The tree map representing total sales which is distributed sub category wise to different units.

Step 1:

To create a dendrogram chart we should create a Path field.

Why is the Path field created: To create a Dendrogram chart we need two points, the start point, and the endpoint. Here 0 and 200 are the start and the endpoints respectively. (0 to 200 is a reference, any number values can be chosen)

Path(bin): This helps to create evenly spread data points between the start and end points, it avoids data duplications and also avoids creating more number of rows therefore accumulating more storage.

So, for this we are going to create a new excel sheet named as 'Path' and have the data: Path 0, 200 as shown below.

Picture 1:

Step 2:

Open Sample Superstore in tableau and cross join 'Path'. Double click on orders to make Multiple connections.

Go to Path - drag and drop the Sheet1 from Path to Orders. Now, click the drop down on Sheet 1 as in picture 2b - To Create join calculation, Enter 1 as in picture 2c and ok. Now to create a calculated field(Cross Join) we should have 1 = 1 in join calculation as in picture 2d.

Picture 2a:

Picture 2b:

Picture 2c:

Picture 2d:

In this way, we are multiplying the data 2 times, whatever rows we have we will now see 2 rows everywhere. One for 0 and one for 200.

Step 3:

Go to Sheet 1 and start working on dataset: Densify your data more by creating a path(bin). This is created to set the thickness for the data set e.g. Ranking.

Now that path and path(bin) is created, lets start creating all the calculated field supporting this dendrogram chart.

Sum of Sales Calculation: This will give the sum of each of the category.

Total Sales Calculation: This will give the sum of total for all the categories.

Percentage Calculation

Percentage Ratio

Rank Calculation

X Axis: According to the index, X coordinate will be between -6 to 18.

Sigmoid Calculation (for curve)

Y Axis: Decides the desired height of the curve

Size: Size of the Bar Calculation

Step 4:

We have created the calculated fields, let us start working on the graph:

Drag and drop path(bin) to detail and Sub- Category to colors. X coordinate to the column and Y coordinate to the rows.

IN the marks convert it into line chart:

Step 5:

Go to Y right click Edit table calculations: Nested Calculations for the rank we need to select Product sub categories, for Y also we need to select sub categories to get the desired height.

Step 6:

Drag and drop 'Size' coordinate into the 'size'. We will get the graph as shown below.

Step 7:

Under marks: Size calculation -> Compute Using - path(bin). Now again, Sales right click Edit table calculation: Total Sales Nested calculation is calculated along Product Sub-Category also. So, we are selecting both the dimensions. The same way for the Percentage adjusted [Select both path(bin) and Product Sub-Category].

Now the length of the bar graph is shown according to the percentage contribution of sales that we have from each of the Product Sub- Category:

Step 8:

Now, it is time to tweak our graph by formatting the visualization. Right Click on Y axis and go to Edit axis and 'Reverse' the Scale.

Step 9:

1. Remove the X and Y axis headers (uncheck 'Show Headers')

2. Drag and drop 'Product-Subcategory' and 'Sales' in the Label.

3. Allow labels to overlap in Label Format. Change the alignment to Center.

4. Right Click on the sheet and go to format: In the Sheet -> Grid lines, Zero Lines and Axis ticks as 'None'.

5. Format Worksheet Shading as 'Black'.

6. Change the currency as 'Currency (Standard)': English(United States)

7. Convert into 'Entire view' and adjust the 'Size' of the bar graph.

Step 10:

Go to New Sheet 2: Drag and drop '@Sales' into Label.

Format the worksheet shading as 'Black'.

Format the Label font as 'Bold' and font color as 'White'.

Change the currency as 'Currency(Custom)'. Display Units: Millions(M)

Final Step:

Assign/Align both the sheets in the Dashboard. Adjust the position of Total Sales value(floating) to the left side center pointing the Bar graph. Remove the legends from the right side corner. Remove the X or Y axis headers if seen. This gives the final Presentation of the Dendrogram Chart.

Note: Data Densification using Path(bin) and Sigmoid function is used to get those cures in this presentation.

Conclusion:

I hope this pictorial representation of creating a step by step dendrogram chart will help anyone learn the fundamentals easily. Don't forget to leave your feedback in the comments section below. If you like this post please give a ❤️, Thanks for reading!

"If I cannot do great things, I can do small things in a great way." —Martin Luther King, Jr.