For Week 10 of Workout Wednesday, @AnnUJackson shares with us as a challenge, to recreate the incredible design she presented for #IronQuest in the month of February.

The #IronQuest is a project with monthly visualization challenges led by Sarah Bartlett that follows a similar format to the #IronViz competitions. Its objective is to offer people the opportunity to show their best practices and skills in design, storytelling and analysis, without the pressure of choosing a winner, in preparation for #IronViz competitions.

For the past month of February, the theme chosen in the #IronQuest was that of Business Dashboards. I recommend that you do not miss the opportunity to consult the full list of Dashboards on Sarah’s blog.

The final visualization, which is reconstructed in this challenge, is a mosaic map for the United States that allows the interannual comparison of the profit ratio.

And if you already know Ann a bit, you know that she loves Table Calculations and one of the requirements that she very commonly includes in her challenges is: “You are not allowed to use Level of Detail (LOD) Expressions! ”

And if you know me a little, you know that I love LOD calculations and although I’m aware that in certain challenges we are asked to use Table calculations, many times in the middle of the challenge I find myself writing an LOD calculation and noticing I delete it and change the switch to find a solution with Table Calculations.

I consider important the balance in the handling of the different techniques, for which, I thank the previous challenges in which, Andy Kriebel, Luke and Ann have motivated me (or force me, ji ji) to use Table Calculations.

This does not mean that I’m requesting that in each challenge we put such a restriction, but I definitely consider that in certain challenges, the use of table calculations offers us interesting lessons.

Next, I share the steps that I followed.

Step 1: Join the original data with the table with positions (x, y) of each state.

For the construction of a mosaic map for the United States, I have a file with the positions or coordinates in which each state must be located.

Then the first step is to make the union between the Orders data table of the special SuperStore file indicated in the challenge, with the position file or coordinates of each State.

Make sure that the data of the coordinates, which in the table appear with the names “Column” and “Row” are considered as “Dimension”.

Step 2: Create the “Profit Ratio” calculation.

 Profit Ratio = SUM([Profit]) / Sum([Sales])

Step 3: Create the variable “Year”.

 Year = DATEPART(‘year’, [Order Date])

Step 4: Create the chart.

Select “Square” type chart and specify that the “marks” in the chart will not be accumulated, this in order that the squares of each year appear superimposed, one over the other.

Move to the shelves of:

• Filter ⬅  “Year” and select years 2017 and 2018
• Columns ⬅   “Column”
• Rows ⬅   “Row”
• Detail ⬅   “State”
• Size ⬅   “Year” order descending
• Color ⬅   “Profit Ratio”
• Text ⬅   “Abbreviation” y “Profit Ratio”

Step 5: Create a quick table calculation of the variation of the “Profit Ratio”.

Select the variable “Profit Ratio” from the “Text” shelf and with the right click create a quick table calculation of type “Difference”.

Once created, edit it and indicate the following specifications:

It’s important that the “Year” field of the “Size” shelf is sorted descending, so that this calculation is executed correctly.

Modify the format of the new field, with the custom format:▲ 0%;▼ 0%;-

Step 6: Adjust the sizes of the boxes.

Adjust the size to use for the pictures of 2017 and 2018, to maintain a harmony between both years.

Step 7: Adjust specifications of the “Label” format

To specify that only the respective labels are shown by 2018 and allow the labels to overlap other marks.

Step 8: Adjust the definition of the colors to be used.

Color palette is Color Brewer Red Blue, (download from Jacob Olsufka’s Tableau Public)