“Each time you have a dream, you never know what it means. You see that open road and never know which way to go” favorite lyrics from my most played song on Spotify. I decided to build Dendrogram chart to show hierarchy of my most played songs. Now without wasting any time, I am giving you the steps to create this chart. Before we proceed, I want to tell viewers that they need to write only one Calculated field and can copy paste all the others as it is. No need to rename anything.

Step #1: Get your data in Tableau.

Step #2: If you are an Alteryx user, once you prepare your data, use Text input tool, make a table with one field name(Path), give values 0 and 200 and use Append field tool to join two data, and skip Step #3.
User who are not familiar with Alteryx can create a table in Excel which look like this-

 

Step #3: Join this table in Tableau. While Joining this table, use Join calculation, and type 1, in both left and right. This will do the cross join. If you are confused, google what cross join is.
The Data part is done now. We now have to build chart in Tableau.
Step#4: Make path bins, and give value 1.

 

Step #5: Now we have to build some Calculated Fields. Please name your 1st calculated field as Value
  • Value – WINDOW_SUM(SUM[*])/2 (// By * I mean the value you want to show in the view)
  • Total Value – WINDOW_SUM(SUM[*])/2 (We write the same calculation, as above and name it Total Value) – We divide by 2 because the cross join duplicate each row

Now copy paste the below Calculations.

  •  Percentage – [Value]/[Total_Value]

  • Percentage Adj – [Percentage]/WINDOW_MAX([Percentage])

  • Rank – RANK_UNIQUE([Value],”asc”)

  • X- ((INDEX()-1)*0.12)-6
  • Sigmoid- 1/(1+EXP(-[X]))
  • Y- [Sigmoid]*([Rank]-(WINDOW_MAX([Rank])+1)/2)/100
  • Size- IF [X] >= 6 AND [X] <= 6 + (10 * [Percentage Adj]) THEN 1 ELSE 0 END

We now done with all the calculations, lets build our view now.

Step#6: Drag path bins to column, and check for on “Show all missing values” is ticked. Then change mark type to line, and drag path from column to mark type path. (See the image below. notice I put path in mark type)
Step#7: Drag X to column and Y to rows, and for both X and Y, click on Compute using Path

 

Step #8: Now put the “Category” you want to show on colors. Once you put nothing will happen, because there are multiple lines on top of each other.

Step #9Now go to Y again, and click Edit table calculation(In the above pic, you the see the option below compute using). In Nested Calculation, tick the following (Instead of Track Name, you have to check for the category you put on colors in the previous step.)

 

 

Step #10: Now its time for us to bring the Value in our view. Drag Size to size, and in drop down select compute using path bins and boom, mike drop, your chart is ready. (You need to do the labelling part by yourself.)

 

If you are curious to understand why we select numbers in our calculation, and how to modify our dendrogram shape, you can visit the website- https://medium.com/@angela_kokhtenko/how-to-build-a-dendrogram-in-tableau-43897e50142c
This site helps me to understand everything about Dendrogram. If you are from strong math’s background like me than you can visit- https://www.cleartelligence.com/post/fun-with-curves-in-tableau-part-3-sigmoid-curves

The Data School
Author: The Data School