DS19’s penultimate project saw us working with data from the National Student Survey. My team were tasked with creating a dashboard which allowed the user to compare the results of the NSS between universities.

The Aim

I wanted to create an interactive chart which allowed you to compare one university’s survey responses to another’s. To do this I decided to use a bar chart to represent my selected university’s values and then reference lines for the comparison university’s values.

My first thought on how to do this was to make the bar chart with university as a filter and control this selection using a filter. Then to have the reference lines controlled by a parameter and associated calculation.

The Problem

When I built this chart the reference lines were only appearing when I selected the same university for both the bar chart filter and the reference line parameter. After messing with the calculation for the parameter the issue turned out to be related to Tableau’s Order of Operations.

Please see below a diagram of Tableau’s Order of Operations with functions and the order to which they are computed:

As I was using a filter for my universities ( a Dimension Filter) this was occurring before my parameters and calculations. This means that when I was trying to use my parameter the data-set had already been filtered to a specific university (the Dimension Filter). This resulted in the parameter only being fed one University’s values and so the reference lines were only visible for the uni that had been filtered for.

The Solution

In order to get around this I needed to include the calculation controlling the parameter in a fixed LOD. As you can see from the Order of Operations, Fixed LOD happen before Dimension Filters in Tableau.

To write this fixed equation I want to say: ‘for every question group, question and year give me the average actual value if the provider is equal to the provider chosen in the parameter’. Which looked a bit like this:

{FIXED [Question Group], [Year], [Question] : AVG(if [Comparison Uni]= [Provider] then [Actual value] end)}

To reiterate Fixed LODs come before dimension and measure filters which means that these calculations look at all of the data before any of the filters are applied. This allows the reference lines to change before the filter is used for the bar charts.

Please find the finished dashboard on my Tableau Public here.