Workout Wednesday – Level of Detail vs. Table Calculations

I love Workout Wednesday. It doesn’t appear to have the traction of community behemoth Makeover Monday yet, but I really hope it starts to gain an audience. I like it because it forces you to deliver to a specific brief.

This week, the task is this, and I’m partly to blame as I’d asked for an opportunity to fiddle with Level of Detail (LoD) and Table Calculations. I’m still new to the Tableau party, and LoD was a fresh release when I started to learn the tool, and I’m a smidgen more au fait with the mechanics of LoD compared to table calcs.

Andy Kriebel dismissed this challenge as a bit of a simple one, but it still caused me some consternation and taught me a few things, so it was a worthwhile exercise for me at least.

Step one – figure out the LoD calc. I always like to build out tables so I can see what I’m doing:

The relevant calcs here are:

By Match Day, what is the largest (maximum) Points Total?

As you’d expect, after 1 match the highest points total was 3 (as you get 3 points for a win in Premier League football). So after 5 matches, at least one team has a 100% record (15 points) and it was the sixth game by which the the final team lost its 100% record.

So if we know the maximum cumulative points total at each Match Day, we can extend that calc to identify if a specific team has matched that total:

If your cumulative points total is the same as the overall maximum cumulative points total for that Match Day, flag it with a 1, otherwise it’s a 0

Arsenal hit the top of the league after ten matches and stayed there for at least three games, as shown in the first image in the post. That’s what Arsenal do. Every season. When the going gets tough, Arsenal get going (down the table).

Naturally, if you then SUM() that calc, you get what you need. The labels are just cell level Reference Lines with some formatting:

A tricky bit is the Axis title, to denote that it’s a LoD calc. To do this, I knew I’d need a dual-axis to force an upper axis into scope for me to tweak. I assumed it was the old AVG(0) trick, but it wasn’t as you can’t synchronise an AVG(0) with an ordinal axis. To get round that, use SUM(0) instead, which you can sync:

Then you can just remove the tick marks from the axis, and tweak the Axis Title to be what you want. Excellent – LoD is done. Bummer – table calc to come. Let’s try and leverage the same approach with viewing data in tables, and indeed the same logic in our calc.

First task is to identify the maximum cumulative points by match day:

Where I always used to (and still regularly do) trip up with Table Calcs is with the “at the level of” bit. Andy wrote a great post and shared a great video about this:

Initially, my default table calc does this:

Leicester had 50 points after 24 games, so the 50 represents the maximum total after the 24 Match Days. I need to add a couple of things into the context of the calc:

This looks sensible, and it mirrors the phasing of my [Matchday Max] LoD calc:

So all that remains is to amend the table calc to include the same sort of logic as the final LoD calc:

And then the Compute Using needed to be configured:

So you can see that where the Points Total matches the Table Calc Cumulative Match Day, a 1 is drawn, else it’s a 0. When you start to build the second bar, you need to make sure that you have the requisite Match Day field on Detail or you won’t be able to Compute Using the basis required. Thereafter, it’s just a case of replicating the steps of the first chart to create the Axis title and you’re done.

Hey Charlie,
I really liked the way you explained this workout. I was able to do it following your instructions, however, my graph on right doesn’t show proper labels. It would be great if you could help me in adding the labels to the second chart

“The labels are just cell level Reference Lines with some formatting” – so if you just right-click the axis, select Add Reference Line, tick Per Cell, set Label to Value and Line to None and you should have what you need.