3. INCLUDE Expressions

Overview

With INCLUDE expressions, you can access deeper levels of granularity in the view, which I'll show you how to do with a simple bar-chart in this lesson.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

How to calculate average revenue per state by region using INCLUDE

- In this lesson, we use an INCLUDE expression to access the state layer, below the region layer (showing in the view)
1 Drag the region into the columns shelf
2 To access state related data we use the INCLUDE expression in the rows shelf
3 The INCLUDE expression is as follows: AGG(AVG({INCLUDE[State]:SUM([Revenue])}))

Transcript

Now that we have a theoretical understanding of LOD calculations, let's apply this knowledge in Tableau beginning with the include expression that enables us to access dimensions that have a higher level of aggregation than the view. To write an include expression, we'll create an equation in Tableau that looks something like this. We open parentheses which represents an LDO expression. We then include the keyword which in this case is include and then we write the dimension that we wish to include in the view which in this example is state. I'll then write a colon and ultimately the aggregate expression. So that we get a strong understanding of the include expression, let's now move to Tableau and run through a simple example.

In Tableau I'd like to examine average revenue by salesperson, so I'll take the Salesperson and add to the Rows shelf.

Then I'll take Revenue and place in the Columns shelf and this gives me a simple bar chart.

To flip from sum to average, I'll use the dropdown.

And this shows me each salesperson and an average revenue figure next to that person.

But what does this figure actually mean? Is the average revenue by customer, by state, in total, by year? Well, let's look at the raw data to find out. I'll right click and go to View Data and if I go to Full Data, I can see that the company name is unique as I scroll down through the column.

And so, each row corresponds to an individual customer. And in Tableau, the calculation will default to row-level data, so for Hall, the average revenue per customer is 19,621.

But what if I want to calculate the average revenue by state? To do this, I need to include state in the view even though the current view has a much more granular level of detail present which is row-level data.

To include state in this view, I'll need to write an LOD expression. So, I'll double click in the shelf and delete the current formula.

And I'll start by opening parentheses and writing include.

Next, I'll include the dimension which is state, write a colon and now I need to perform my calculation. To calculate the average revenue by state for each salesperson, I need to sum the revenue up to state level.

So, I'll simply write sum of revenue and then close my parentheses and then apply by hitting control and enter.

When I do this and ensure that the average function is wrapped around my LDO expression, I now know for each salesperson the average revenue by state that they have.

Looking at the LDO expression I can see that revenue is summed at the current row level of data up to a state total and these state totals are then averaged to give me my chart.

Let's now repeat this calculation but I'll use a different level of detail, I'll use region.

I'll start by dragging revenue into the Columns shelf and then I'll double click.

I'll start by writing an average function and inside this, I'll have my LDO expression which again, would be include and I'll include region.

Then I'll write a colon and then I have sum of revenue and then I'll make sure to close my brackets. As you can imagine, because include is aggregating or grouping data, we tend to use sum of revenue a lot as our calculation in these LOD formulas. However, we tend to vary the calculation that happens upon the LOD expression, in this case, average.

For example, instead of wanting to know the average state revenue by salesperson, I may want to know the maximum state revenue by salesperson. To do this, I'll jump back into my formula and put a max function around the LOD expression.

And when I do this, I can see that for Baines the maximum state revenue is 259,702.

If I want to see the minimum state revenue for all sales people, this time I'll adjust my second calculation.

I'll change the dimension to state and I'll change the operation from average to min.

And as you can see for some of our sales people such as Schwartz and Uncurl, we've got quite large difference between the max state revenue and the min state revenue.

Although these LOD expressions are quite easy to write it's worth taking the time to experiment with these expressions to make sure that you understand the syntax fully. In the next lesson I'm going to move on to exclude expressions where instead of adding dimensions to a view, we remove existing dimensions.

>
Formulas in Tableau
Level of Detail Expressions

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial