Workout Wednesday: practicing level of detail and table calcs in Tableau

I don’t usually participate in Tableau #WorkoutWednesday, but this week the chart we had to reproduce featured my team so I had to give it a go!

Here is the finished chart, which is also available on Tableau Public:

How many times has a team been top of the premier league

 



The source data included the match day (week), team name and their total points at the end of that match day. Here are the first two rows to give you an idea:

Match day Team Total points
1 Arsenal 0
2 Arsenal 3

So the challenge is to:

  1. Look at the total points each team has at the end of a game week
  2. Rank them to find out which teams are top that week (NB: in actual fact only one team would be top and goal difference would be applied if there was a tie, but the purpose of the challenge wasn’t to get into that. Also it was pre-acknowledged that the data didn’t cover a full season).
  3. Count up the number of times each team was top

The idea was to do that once using level of detail calculations and again using table calculations. Now to be honest both of these do my head in at times given where I’m at with learning Tableau so it’s a great challenge to try even if the mighty foxes weren’t featured!

Lets start with the table calculations because they are sometimes perceived to be harder.

Firstly I calculated the rank for each team based on their total points at the end of each match day, using RANK working along the list of teams. I.e. ”give me the rank of each team based on their total points”:

calc1

 


Next I calculated the number of match days each team was at the top using a running sum of :1 when the team was top and 0 when they weren’t, this time along match day.  I.e. “give me a running count of the number of times the team has been at the top of the league as we progress through the match days”:
calc2



The final piece of the puzzle was to be able to filter the results down to the final match day, without impacting the table calculations above (filters are generally applied before table calculations in Tableau so you apply your filter and all your table calculation seem to break! They don’t really, they’re just being applied on a subset of the data now, but it feels like they break when you’re new to them). The trick here – and I wish I could remember where I picked this up so that I could credit that person – is to filter on another table calculation. That way the filter is applied after table calculations are done. The calculation is pretty simple: “is this the last match day?”:

calc3



After taking this approach (one calculation along team, the next along match day, and then filtering to final match day) I saw that other people had stopped at a version of the first table calculation. They could then plot a series of stacked bars (of value 1) for each week where a team was top. Cunning! And it saves getting your head around the double table calculation right? But on the downside you’ve got stacked bars where you didn’t really need them, and you need to use a reference line for an overall label. On the upside your head didn’t explode due to table calculations on top of table calculations. And the next poor analyst who has to maintain your workbook doesn’t curse you to all of your colleagues!

So what about the level of detail version? Well it’s basically doing the same thing, but in a different way of course. I haven’t broken this down into multiple calculations so bear with me. Starting in the middle with the first calculation {FIXED [Match Day]: MAX([Points Total])} I’m saying “tell me what the maximum total points were, regardless of team given that my viz is at the team level of detail, on any match day”* by fixing at the match day level of detail. We can then identify the teams with a points total that matches this max. These are the teams at the top of the table for that match day. We given them a 1 and others a 0. We can then SUM the 1s and 0s up at the Team Name level of detail (i.e. across all match days) to give us a final count:

calc4



*I wonder if I should have used EXCLUDE instead of FIXED in the level of detail calculations, but the above worked so I stuck with it.

Another little trick in the workout was to get the axis labels to appear at the top rather than the bottom (in Tableau continuous measures get a bottom axis whereas discrete measures get a category header at the top). The trick here, which I had to Google (leading me back to Andy’s blog of course!) was to duplicate the measures and drag them on to the top of the panel. I then had to clear the axis label and marks at the bottom (you can’t just unclick “show header” as you lose the top labels too).

All in all a fun challenge and some great Tableau calculation practice.