By: Rosario Gauna @rosariogaunag

The challenge that @LukeStanke prepared for week 50 of #WorkoutWednesday is fresh out of the oven and definitely a challenge that promised great fun.

Link to the challenge

The challenge asks to create a trellis-chart for “State” using pie graphs.

A multiple chart or trellis chart is a series of similar charts or tables that use the same scale and axes to show different partitions of a data set, allowing you to easily compare and contrast the values ​​between the charts.

The challenge also requires allowing the user to select one of the “Sub-Categories”. Based on the selection of the user, the Pie of each of the “States” can be divided into three parts:

  • The percentage of sales of the “Sub-Category” selected within the sales of the State.
  • The percentage of sales of the rest of the “Sub-Categories” that correspond to the same “Category” to which the “Sub-Category” belongs, selected by the user.
  • The percentage of sales remaining to complete 100%.

To add a greater degree of emotion, the following features can be added to the challenge: 1) The “Category” should not be defined using a rigid coding of the “Sub-Category”; and 2) Sort the “States” based on the sales percentage of the “Sub-Category” selected by the user.

Next, I share the procedure that I used:

Step 1: Calculate for each “State”, its position or coordinates. That is, in which column and row each “State” should be displayed.

The SuperStore data set has 49 “States” included (not Alaska or Hawaii, but the District of Columbia is included) which provides a perfect data set for a 7 × 7 grid. Because the grid size will not change, you can use a short version of the calculation of the coordinates, leaving fixed the value of 7 States plotted by row.

COLUMN =

INT( (Index() – 1) % 7)

ROW =

INT( (Index() – 1) / 7)

 Step 2: Create a parameter for the user to select a “Sub-Category”.

Define the parameter with the name “Choose Sub-Category” and its values will be taken directly from the “Sub-Category” field.

Step 3: Calculate the Category to which the “Sub-Category” selected by the user belongs.

An LOD calculation will be used to set the value of the “Category” to which the “Sub-Category” selected by the user belongs.

Selected Category =

{FIXED :

MAX(IIF([Sub-Category] = [Choose Sub-Category],  [Category],  NULL))}

Step 4: Create a dynamic dimension that represents each of the three parts in which the Pie is subdivided.

Slice =

IIF([Category] = [Selected Category],

IIF([Sub-Category] = [Choose Sub-Category],  “1st”,  “2nd”),  “3rd”)

Step 5: Calculate the values of the total sales by “State” and the total sales by “State-Slice”.

LOD calculations will be used for its definition.

Sales by State = {FIXED [State] : SUM([Sales])}
Sales by State & Slice = {FIXED [State], [Slice] : SUM([Sales])}

Step 6: Calculate the Sales Percentage that will be displayed in the tooltips of the Pie chart.

% Sales = SUM([Sales]) / SUM([Sales by State])

Step 7: Calculate the sales percentages of the first and second slice of the Pie.

These two fields will be used to order the display of the “States”. For its definition, LOD calculations will be used.

% Sales 1st Slice =

{FIXED [State] :

AVG(IIF([Slice] = “1st”,

[Sales by State & Slice] / [Sales by State], NULL))}

% Sales 2nd Slice =

{FIXED [State] :

AVG(IIF([Slice] = “2nd”,

[Sales by State & Slice] / [Sales by State], NULL))}

Step 8: Calculate the “Sort” field.

This formula will help to display first, all the “States” that had sales in the selected “Sub-Category”, ordered by the percentage of sales of the first slice of the Pie, that is, by the percentage of sales of the ” Sub-Category “selected.

Subsequently, those “States” that do not have sales in the selected Sub-Category will be displayed, ordered by the percentage of sales of the second slice.

Sort =

IIF(NOT ISNULL([% Sales 1st Slice]),

[% Sales 1st Slice] + 100,

[% Sales 2nd Slice])

Step 9: Calculate the Color field.

Color =

[Slice] +

IIF([Category] = [Selected Category],  “-” + [Selected Category],  “”)

This formula will allow to set independent colors by “Category”, for the first and second slice of the Pie. In the case of the third slice, the gray color will always be used, regardless of the selected category.

Picture2

Step 10: Define the Trellis-Chart

For the PIE graph move to the shelves of:

  • Columns   ⬅“Column” (convert to discrete)
  • Rows   ⬅“Row” (use reverse scale)
  • Detail   ⬅“State” ordered by the “Sort” field
  • Color   ⬅Color
  • Angle   ⬅SUM(Sales)
  • Tooltips   ⬅AGG(% of Sales) and ATTR(Legend)

For the table calculations of the variables “Columns” and “Rows” the following definition will be used:

Picture3

For the “Text” graphic move to the shelves of:

  • Rows   ⬅Row-0.5 (use dual axis and synchronized)
  • Detail   ⬅“State” ordered by the field of “Sort”
  • Detail   ⬅“Color”
  • Color   ⬅“Selected Category”
  • Text   ⬅“State” and “% Sales of 1stSlice”

Picture4

Step 11: Calculate the Legend field.

This formula will allow to calculate the dynamic signs that contain the meaning of each one of the three colors used in Pie graphs.

Legend =

IIF([Category] = [Selected Category],

IIF([Sub-Category] = [Choose Sub-Category],

[Sub-Category],   “Rest of ” + [Category]),   “All others”)

Step 12: Define the secondary graph to show the dynamic legends with what each color means in the Pie graphs.

Move to the shelves of:

  • Columns   ⬅Color
  • Color   ⬅Color
  • Text   ⬅Legend

 Step 13: Adjust tooltips and formats.

Ready!

Link to Tableau Public 

Picture1

I take this opportunity to thank @LukeStanke for sharing this challenge that exceeded my initial expectations of fun.

If you have any questions about the blog, do not hesitate to contact me on twitter (@rosariogaunag)

Regards!

Rosario Gauna

Advertisements