At The Information Lab we are organising Q&A sessions with organisations which are interested in Tableau and would like to learn more about it. In one of these sessions an attendee was creating a table to show the orders of the last N days and asked me the following question:
“I have [Order Date], [Customer Name] and [Order ID] in my Rows shelf and [Sales] on the Label shelf. I would like to include the Average Sales per Customer in my view as well. This will allow me to quickly compare the orders of the exact dates with the average order level of the same customer(s). Is it possible?”
The answer is again a LOD expression because we need to include fields and calculation(s) which are based on different levels of detail in the same worksheet.
Different Levels of Detail? Which? Why?
The First View includes a range of exact [Order Date], the [Customer Name] and the [Order ID]. Therefore, placing the [Sales] in the view will give us the total amount that the customer ordered in the given day. The range of exact dates is controlled by the [Order Date] field which is placed as a measure filter on the Filters shelf.
/First view/
The requested Average Sales amount per Customer needs to be calculated across all rows without being impacted by the dimensions on the Rows shelf and the date filter. This is a different level of detail compared to the first bullet point. Nevertheless we want to display it in the same worksheet. Therefore, a LOD is the solution!
/Final View/
Calculations
Now let’s have a look on the calculations of the Final View.
Avg Sales per Customer:
The LOD calculation will return the Average Sales amount (see AVG([Sales]) per Customer (see [Customer Name] before the colon) while considering all available data. Taking into account Tableau’s Order of Operations we know that the measure (date) filter will apply to the first column which is the [Sales] but not to the [Avg Sales per Customer] because the latter is higher in the query pipeline.
Percentage:
The above calculation allows us to see how much is the [Sales] amount of the first column compared to the [Avg Sales Amount per Customer] of the second column. In the Final View, I have coloured the rows of the table using the [Percentage] field.
You can find the resulting workbook here if you want to take a look.