Luke Stanke provided his last challenge of #WOW2020 for the year with this viz to utilise Measure Names & Measure Values. This plots Cost against Sales per Sub-Category (the circles), which are then bounded by a lozenge. The Sales circle and the lozenge are coloured based on profitability, and the lozenges are labelled by Profit, although the position of the label is also based on profitability.
Obviously as the title of the challenge suggests, using Measure Names & Measure Values is key to this. As usual I’ll try to just pull out the main points I think you might find most tricky.
- Building the core chart
- Aligning the Sub-Categories against a horizontal line
- Colouring the circles & lozenge
- Positioning the labels
- Formatting the labels
- Creating a border around the white Cost circle
Building the core chart
You’ll need to create a calculated field to store the cost
SUM([Sales]) – SUM([Profit])
Once done, then add Sub-Category to Rows and sort by descending by Profit. Add Measure Values to Columns and filter just to Sales and Cost. Change the mark type to circle.
Add a 2nd instance of Measure Values to Columns and change the mark type to Line. Add Measure Names to Path.
Then make this chart dual axis and synchronise the axis. This will give the base chart.
Aligning the Sub-Categories against a horizontal line
Whenever you add a blue discrete pill to the rows or columns, you’ll get a ‘header’ pane, which means the label for the pill is by default positioned in the centre of a row, as demonstrated below if I add row dividers to the chart
The requirement is to show the row label aligned with a horizontal line. To achieve this, we need to add a MIN(0) field the Rows. Adding this green pill, creates an axis which has a 0 horizontal 0 line, which is positioned exactly in alignment with the row label
Removing the row dividers, and uncheck Show Header on the MIN(0) pill, and the right aligning the row label, we get the desired result.
Colouring the circles & lozenge
The circles are either coloured white for Cost or dark red or green for Sales depending on the profitability of the Sub-Category.
So we need a new calculated field
This returns true or false.
On the circles Measure Names mark card, add Is Profitable to the Colour shelf. If it’s not already there, then add Measure Names to the Detail shelf, then select the detail icon and choose the colour icon to also add Measure Names to the Colour shelf
This has the effect of giving you 4 colour combinations
which can then be edited to set the 2 combinations relating to Cost to white, Sales & True to dark green and Sales & False to dark red (at this point, you probably want to be setting the background colour of the whole worksheet to light grey (right click on sheet, format, select the Shading icon)
For the lozenge, you just need to add the Is Profitable field the the Colour shelf of the line Measure Names card, and adjust the size of the to make it slightly bigger than the circles (you may need to move marks to back to get the lozenge to sit behind the circles).
Positioning the labels
So the tricky thing about the labels is where they are positioned – they are to the right for the profitable sub-categories, and to the left for the non-profitable sub-categories. When you add a label, you can’t provide that sort of conditional logic to control the positioning. However, we happen to have 2 sets of marks cards (as dual axis), and so can use different fields to apply the labelling.
Label Profit is +ve
IF [Is Profitable] THEN SUM(Profit) END
This will only return a value for profitable sub-categories, otherwise the field will store blank.
Label Profit is -ve
IF NOT([Is Profitable]) THEN SUM(Profit) END
Conversely, this will only return a value for the non-profitable sub-categories.
Add the Label Profit is +ve field to the Label shelf of the Circle marks card. Adjust the positioning to be middle right, and adjust the settings as below (tbh this was just a bit of trial and error to find this worked :-)). You may want to additionally add a few <spaces> to the start of the label text to add a bit of breathing room.
Then on the Line marks card, add the Label Profit is -ve field to the Label shelf, but this time choose to Label Line Ends, and select Label end of line. Note – you can try to apply the same settings as above, but I found for some reason it increased the size of the mark around the line end… weird…
Formatting the labels
Right click on the Label Profit is +ve/-ve fields created and custom format with
Creating a border around the white Cost circle
Finally, a sneaky requirement was to add a border around the white circle that represented Cost only. This implied that using the formatting options on the Colour shelf that allows a border to be added to the mark wasn’t going to work, as this will apply to all marks and not just to the Cost circle.
I handled this by creating a 2nd instance of the Cost field (right click Cost and select duplicate).
I then added this Cost (copy) field to the Measure Values section of the chart on the left hand side. This doesn’t appear to do much, since the mark is directly on top of an existing mark, but you will see the Colour legend associated to the Circle marks card change and add 2 new entries.
I changed the colours of the 2 new entries in the Colour legend to a dark grey, then added Measure Names to the Size shelf of the Circles marks card. I then adjusted the range and size of the circles so that the Cost measure is slightly smaller than the Cost (copy) measure. Ultimately all 3 circles are slightly different sizes, but the range is so narrow, it’s hard to tell.
And that’s it. A lovely looking viz with a fair bit going on under the bonnet, but nothing too complex! My published version is here.
Happy vizzin’! Stay Safe!