MMMM.

Week 51 of #MakeoverMonday was a source of rich learning material for me, and when Michael shared his viz, I knew I’d be looking at a combination of excellent design and great logic. As with the dashboards shared by Lindsey Poulter and Ashish Chauhan, I felt there was more than enough to warrant a summary of things I picked up when investigating the workbook.

At a data preparation level, I can tell that Michael has just pivoted the source data. This can be deduced from looking at the Dimensions and Measures in his workbook:

If we exclude calculations (prefixed with “=”), you can see just a Metric / Target, Month and Value. A fourth field is hidden (the original Year format), which Michael has tidied up with a SPLIT function in his Year calc. Replicating that pivot is a simple job, so let’s check out what was done next to prepare the data, in Tableau, for the viz stage.

The first calculation does this:

It’s similar to some of Ashish’s work, but is even *more* efficient. In this case, it cleverly trims the metrics suffixed with bracketed contextual values, AND it removes ” Target” from the result, all in one hit. What does the impact of this look like?

Now I think this is really clever! Michael has done in one calc what Ashish achieved in two. OK, so you’ve got a nice clean Metric Group now, but how do you split out the Actuals from the Targets?

Really logical stuff again. The Level of Detail {FIXED} calc focuses on the Metric Group and Date for the Actuals, and returns the value where the Metric / Target fields doesn’t contain the word “Target”. Simple. Aggregation is a prerequisite of LoD calcs, and the actual operator used doesn’t matter (MAX in this case), as the value is unique at that data point intersection.

As The DC Metro Targets per metric have been flat in the date range covered by the dataset, Michael doesn’t need to FIX on Date, so just focuses on the Metric Group and returns the value where “Target” IS in the Metric / Target field.

Now that the source data has been cleverly tidied up and bucketed into distinct Actual and Target categories, Michael was in a position to start lobbing Dimensions and Measures on the various Tableau shelves. The beauty of this data prep is that actually, Michael’s dashboard simply comprises of the same basic sheet, nine times over with just the Metric Group changed. Let’s look at one of the sheets:

I’ll look just at Rows and Columns for now. Date is an obvious one on columns, and we know what SUM(Actuals) represents. How about that SUM(Focus Month Actuals) calculation? What that results in is the Circles on the chart, showing the same Month of data, but in prior years. It’s still plotted using a Line chart on the marks card, but the Size has been increased to create the circle effect. When I first saw this, I assumed that the LOOKUP function (or similar) had been used:

Nope, much easier than that:

It’s really simple, and again my instinct was to complicate matters. A parameter is used to designate a “Focus Month”. If that value matches the Month of the data, then pull through the Actual, otherwise do nothing. Hence you get that uniform distribution of circles.

OK. Time to compare and contrast these two images:

Bus Fleet Reliability hit its Goal in 2016 of the Focus Month, and the circle is green, whilst the prior Months are white. Conversely, where Bus On-Time Performance did not meet its Goal, the 2016 Focus Month is Red, with all other highlighted months being white. How was that achieved? A boolean calc:

This “Goal Met or Not Met”calculation needs to be looked at, in order to understand its role:

And, working backwards, I need to look at “2016 Focus Month Actuals” too:

Firstly, the calc immediately above is just returning the Actuals of the Focus Month where the Year is 2016.

Reverting our attention back to the “Goal Met or Not Met”calculation, we can see that where rounded Actuals are greater than rounded Targets for Bus, Rail, Elevator and Escalator metrics, then the goal is “Met”. Where it is preferable to be below Target (Crime and Injuries), the calculation is adjusted accordingly.

That calc then feeds into the Circle Colo(u)r(!) calculation, which has three colour possibilities: Met (green), Not Met (red) and White (~~turquoise~~, white). So that is the main element of the chart area covered, but what about the Title areas?

That is one effective Title, isn’t it? There must be a few parts to it?

If I tackle each bit, one by one, here’s how it hangs together.

- Metric Group – we already know what that is
- Goal Context is a separate calculation, which “adds back in” the bracketed context that was stripped out with the original Metric Group calculation. It looks like this:

It’s simple again, but wonderfully elegant and effective. I just don’t think like this and need to learn how to.

3. Goal Title (Met) and Goal Title (Not Met) are also ones I haven’t unearthed thus far:

These need to be separate calculations, as Michael has coloured the results accordingly. Goal Met is green, Goal Not Met is red. You can see this if I take off the highlighting I applied to the original Edit Title image – look at the second line:

4. Similarly, you’ll observe the Circle Met and Circle Not Met calculations in line three above:

5. Everything else relates to calculations already picked up earlier in the post

The dashboard itself is a well crafted, Floating galore, triumph of design. One additional feature therein is the nice little sparkline Legend:

This is really cool. The chart itself it just a mini version of the others, without the Reference Line as its purpose is to inform the audience how to read the “main” charts. It still contains a nice bit of dynamism, as the Floated text box includes the Focus Month parameter, so the Legend updates as the Focus Month changes:

So many nice touches throughout this dashboard, and so much to try to remember!