LOD Use Case – Replacing a Window Average with a LOD

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.