Level of Detail expressions or LOD expressions help users to specify the dimensions used in a calculation without having to include them in the visualization. To showcase the new level of detail expressions in Tableau, we’ll be looking at a dataset containing records about a PC game called Heroes of the Storm.
Each game has a total of 10 players divided into 2 even teams. There can only be one winning team and one losing team. Each team consists of 5 unique characters (aka heroes) controlled by 5 unique players. Each of these players has a rating called MMR that the game uses to measure the players’ skill. The heroes in the game fit into 1 of 4 categories: Tank, Assassin, Specialist, or Support. Each game can take place on 1 of 10 different maps. With the background out of the way, let’s take a look at how these new expressions can help us.
There are three new level of detail expressions in Tableau: FIXED, INCLUDE, and EXCLUDE.
Today, we’ll learn about FIXED. This expression is used most often when you’re calculating a value that uses data outside of the view level of detail. This means that your view is only returning results for a subset of your data, but you’d like that subset to be compared to more than what is shown in the view.
For the first example, we’ll be trying to answer the question, what is each hero’s win rate on a specific map vs their average on all maps? In the context of retailers, this could read as, “What is my profit on a product in a specific state vs the product’s profit in all 50 states?”
You’ll see in this example that we have created a visualization that shows every hero’s average win rate that abides by the map filter. However, we also want each hero’s over all win rate that is map agnostic.
In order to accomplish this, we’ll use the FIXED formula. The FIXED formula looks like this:
{FIXED [<dim>] : <aggregate expression>}
Whichever dimension we fix will be the only dimension taken into account when calculating our measures. In our example, we only care about each hero. We don’t care which map the game was played on, we don’t care about the hero’s class, or any other dimension.
In our case, we’ll use {FIXED [Hero]: AVG([IsWinner])}. If you’re familiar with SQL, this reads like a WHERE clause, i.e. WHERE [Hero] = ‘<Hero Name>’.
Now, we can adjust the map filter and see each hero’s average win rate per map compared to their overall win percentage. Notice in the example above that the overall win percentage hasn’t changed, while the per map percentage does change as we adjust our map filter.
If we wanted to take this a step further, we could create a calculated field that took the difference between their overall win rate and their map specific win rate. A positive result would reflect that they were, on average, better at a specific map and a negative result would reflect the opposite.
Final thoughts on FIXED:
- Your FIXED calculation can have multiple dimensions in it
- Using FIXED ignores view-level filters, but does not ignore context filters, data source filters, or extract filters
- Using FIXED will convert a measure into a dimension
- Using FIXED simplifies the process when comparing measures in your view to aggregate measures in your data source
Credit to Ben Barrett and the rest of the team from www.hotslogs.com for making the Heroes of the Storm data publicly available.
To learn more about level of detail expressions in Tableau, and to see some practical business applications, check out their blog post.