Week Three of #WorkoutWednesday was a war of attrition! Create a single line chart for a State, then replicate it 49 times for the other States in scope. Pleasingly, it wasn’t a technical brain-drain and I managed to get most things right. Here’s a summary of one bit I got wrong, and some other nice things I learned from the challenge this week.

  1. Number formatting to get a “+0” on an axis

The variance from the national average needed to be plotted on a scale like this:


For the National Average, I used this Level of Detail calculation:


Focus on Year. Add up all the [Unemployed] values and divide that figure by the sum of all the [Civilian Labor Force Population] values

The variance from that average was simply:


MAX() is required to ensure that we are comparing two aggregated values – without it the calculation is not valid. We can do this because the MAX of the [National Average] can only be the National Average – it’s a unique value.

As far as the axis scale is concerned, looks easy? Just a straight bit of formatting? Yes:

Screen Shot 2017-01-20 at 19.12.29.png

If, for some odd reason, you switched this so the negative formatting was before the positive, the axis would end up looking like this:


    2. Centre aligned chart “titles”

The bit I stuffed up! Spot the problem:


Alaska and Connecticut have nicely centred titles, but Alabama and Colorado are shifted over to the left. Why is that? Well, I used centred worksheet titles, and that centring occurs across the breadth of the chart – axis included. Alaska and Connecticut don’t need the y-axis in this viz because the viewer can discern the scale from the leftmost State, so the State text is aligned in those instances.

I didn’t really know how to resolve this (aside from doing some dodgy manual spacing until things looked “just right”), so downloaded the original workbook. To recreate Andy’s methodology, I just needed to do this:


State(Upper) is a basic calc to convert the State name to upper case:


The important thing is that as a Discrete (Blue) field, it creates a Header:


See how the grey shaded area around Alabama does not extend to the y-axis? As a result, ALABAMA is centrally aligned over the x-axis itself. Just formatting the font size and removing the “Field Labels for Columns” tidies it up. A bonus of this approach is the white space saving. Compare and contrast the two methods:

On the left is the “Header” version. Obviously the centred State name is obvious, but note too the proximity to the vertical gridlines compared to the image on the right – it is closer to the chart itself.

    3. Reference line vs. reference band

Initially, I used a Reference Line and shaded above it. Compare a reference line on the left, to a reference band on the right:


I end up stating the obvious quite a bit, and will do so again. The reason the reference line version (left) extends beyond the band (right), is because we aren’t in control of the range of the colouration. With a reference band you can stipulate the Band From and Band To values, so that’s why the Band option was appropriate in this case:

Screen Shot 2017-01-20 at 20.23.03.png

An unimportant observation is that I don’t know how optimal the User Interface is with Reference Bands. In order to set the Band to being based on a Constant, we need to navigate to the right to select the relevant option:


I don’t have much experience with using the Analytics Pane, but didn’t find this wholly intuitive.

That’s it – there are three tips I picked up from #WorkoutWednesday this week!