Part of my (daily) job is to answer the questions that our clients submit at the support portal of The Information Lab. Last week I received the following question:
“I managed to create a table calculation which shows the Profit / Quantity ratio per country. However, I didn’t manage to present the window average of all countries in a text box like the other KPI’s within the Management dashboard. When I take the country out of the Row shelf my average is no longer correct. How can I solve this?”
The answer lies within the Level of Detail (LOD) expressions. The LOD expressions allow us to create calculations at a level of detail other than the view level.
But let’s start from the very beginning. Using the EU Superstore, I created the initial calculation which is:
Window Average
Then the window average:
I placed both in the view and made sure that the Window Average computes using Table down:
(Note that I have selected 0 decimal places for all measures in the workbook)
The result is an average [Profit / Quantity ratio] of 4 considering all the countries included in the dataset: ((22+20-19+19…+18) / 15) which equals to 4.
Now if you want to present the value of 4 in a text box as a KPI and take the Country field out of the Rows, the [Profit / Quantity ratio] becomes 10 instead of 4:
The reason is that without the Country field as a level of detail, the calculation SUM(Profit) / SUM(Quantity) now becomes (372,830 / 37,773) = 10. In addition, we have only 1 row in the table and thus the value of the Window Average is the same: 10.
Level Of Detail Expressions in Tableau
I fixed this by using a LOD calculation:
Afterwards, I created a new worksheet and placed the LOD calculation in the view:
The value of 4 for the Profit / Quantity ratio is right there!
The reason is that the LOD expression calculates the [Profit / Quantity ratio] for every country in the dataset even if the Country field is not in the view. This is happening because I have put the Country field in the first half of my LOD calculation! Therefore, Tableau calculates ((22+20-19+19…+18) / 15) in the background and not (372,830 / 37,773).
You can find the resulting workbook here if you want to take a look.
PS. Details regarding the LOD expressions can be found here.