For week 21 of #Work on Wednesday, Ann Jackson challenges us to a new adventure to the dark side, with a exercise full of details.

In the exercise it’s required to create a plot of type dot plot / strip plot. A dot plot is a statistical chart that consists of data points represented on a simple scale that normally uses filled circles.

The main requirements of the exercise are:

• Represent each Manufacturer within each Sub-Category with a circle.
• Each circle will be located on the “x” axis according to its profit ratio
• The size of the circle will be determined by the level of Sales.
• Two colors will be handled in each circle. 1) In the upper part the color will represent the profit ratio; 2) In the lower part of the circle, the color will serve to identify the Manufacturer with the best level of sales within the Sub-Category.
• The name of the Manufacturer with the highest level of sales in each sub-category, should appear or be displayed.
• The names of the Sub-Categories are shown on the chart, invading the initial part of the axis.

Next, I share the steps that I followed.

Step 1: Create the variable “Profit Ratio”

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

Step 2: Calculate the Top 1 Manufacturer in Sales.

It will have two values: “True” for the best-selling Manufacturer and “False” for the others.

 Top 1 in Sales?  = RANK(SUM([Sales])) = 1

Step 3: Create the variables “Top Label Display” and “Top Label Tooltips”

Both variables will only have value when it comes to the Manufacturer with higher sales levels.

 “Top Label Display”  = IIF([Top 1 in Sales?], ATTR(UPPER([Manufacturer])), NULL) “Top Label Tooltips”  = IIF([Top 1 in Sales?], ” TOP SELLING MANUFACTURER WITHIN SUB-CATEGORY”, NULL)

At this point we finish with the definition of new variables and we will face the main challenge of this challenge:

The creation of the chart

How do you handle two colors for the same circle?

How do you put the name of the sub-category at the beginning of the x-axis?

Wow! Your head tells you that it is simply impossible and / or you do not know where to start. Do not worry, I’ll give you the first clues: It is not a single chart and one of the chart uses more than one axis.

Now try to reframe the solution with these clues. Best?

Below I share the detailed steps for the creation of the charts.

Step 4: Create the main chart of circles type

Move to the shelves:

• Filter ⬅   “Region” and with the right click select Context filter.
• Columns ⬅   “Profit Ratio”
• Rows ⬅   “UPPER([Sub-Category])” sorted “desc” by “Profit Ratio”.  The option “Show Headers” is deselected.
• Rows ⬅   AVG(1).  The option “Show Headers” is deselected.
• Detail ⬅   “UPPER([Manufacturer])”
• Color ⬅   “Profit Ratio” use color palette: CB_RdYIGn
• Size ⬅   “SUM(Sales)”
• Tooltips ⬅   “Top Label Tooltips”

For “Sub-Category” and “Manufacturer” it is required that the names appear with capital letters, for which you can create new fields with their definition in capital letters or directly capture the formula in the shelf. For this exercise I used the second option.

A “Border” option must be selected on the “Color” card.

You must edit the axis of AVG (1) to adjust it between a range of 1 to 2, which will give the effect of cutting the circle in half, thus achieving the effect of a higher semicircle.

The “Top Label Tooltips” field will be edited to be selected to be calculated by specific dimensions: UPPER ([Sub-Category]) and UPPER ([Manufacturer]) and reinitializing each UPPER ([Sub-Category]).

Step 5: Create an additional axis in the chart

The definition of the shelves of the second axis is:

• Rows ⬅   AVG(1).   The option “Show Headers” is deselected.
• Detail ⬅   “UPPER([Manufacturer])”
• Color ⬅   “Top in Sales? Black color for True and White color for False
• Size ⬅   “SUM(Sales)”
• Tooltips ⬅   “Top Label Tooltips”
• Label ⬅   “Top Label Display”

A “Border” option must be selected on the “Color” card.

In the second axis, AVG (1), two tricks will be used:

– As in the first axis, the second axis will be edited and the axis will be adjusted between a range of 1 to 2, which will give the effect of cutting the circle in half, having a new semicircle.

– The second step is to invert that semicircle to achieve the lower semicircle and this is achieved by adjusting the axis with the “Reversed” option.

The table calculations “Top 1 in Sales?”, “Top Label Tooltips” and “Top Label Display” will be edited to select which will be calculated by specific dimensions: UPPER ([Sub-Category]) and UPPER ([Manufacturer]) and reinitializing each UPPER ([Sub-Category]).

As more than one chart will be used to achieve the complete design, the format of the worksheet must be edited and in the shading option of the worksheet indicate the option “None” to give the transparent effect.

You must also adjust the shading at the Row Banding, so that a row without shading and a row with shading is interleaved.

Step 6: Create the second chart with the names of the Sub-Categories

Move to the shelves:

• Filter ⬅   “Region” with the right click identify it as a Context filter.
• Rows ⬅   “UPPER([Sub-Category])” sorted “desc” by “Profit Ratio”. The option “Show Headers” is deselected.
• Label ⬅   “UPPER([Sub-Category])”.

In this second chart you should also give the transparency effect, by using the “None” option in the shading of the worksheet.

Step 5: Join both charts in the dashboard.

Taking advantage of the functionality of transparent chart it’s possible to superimpose charts, to simulate that it is a single one. For this, use the option of floating elements within the dashboard and adjust the position (x, y) and the size of each chart in order to create the effect of a single chart.

To conclude:

One of the main challenges this week was to create circles with different color definitions for the upper half circle and the lower half circle.

The key to the solution was to simulate the circle, joining two semicircles.

The semicircles come from charts of circles, where the definition of their axes is adjusted to create the desired effect:

– Adjust the axis with truncated ranges, to visualize only half of each circle, that is, a semicircle.

– Adjust one of the two axes to use the inverted scale, in order to invert the semicircle and simulate that it is the lower part of each circle.

Thank you very much and if you have any questions about the post, do not hesitate to contact me on Twitter (@rosariogaunag)

Regards!

Rosario Gauna