Which calculation should the consultant use?

A client wants to see the average number of orders per customer per month, broken down by region. The client has created the following calculated field:

Orders per Customer: {FIXED [Customer ID]: COUNTD ([Order ID])}

The client then creates a line chart that plots AVG (Orders per Customer) over MONTH(Order Date) by Region. The numbers shown by this chart are far higher

than the customer expects.

The client asks a consultant to rewrite the calculation so the result meets their expectation.

Which calculation should the consultant use?
A . {INCLUDE [Customer ID]: COUNTD ([Order ID])}
B . {FIXED [Customer ID], [Region]: COUNTD ([Order ID])}
C . {EXCLUDE [Customer ID]: COUNTD ([Order ID])}
D . {FIXED [Customer ID], [Region], [Order Date]: COUNTD ([Order ID])}

View Answer

Answer: B

Explanation:

The calculation {FIXED [Customer ID], [Region]: COUNTD ([Order ID])}is the correct one to use for this scenario. This Level of Detail (LOD) expression will calculate the distinct count of orders for each customer within each region, which is then averaged per month. This approach ensures that the average number of orders per customer is accurately calculated for each region and then broken down by month, aligning with the client’s expectations.

References: The LOD expressions in Tableau allow for precise control over the level of detail at which calculations are performed, which is essential for accurate data analysis. The use of{FIXED}expressions to specify the granularity of the calculation is a common practice and is well-documented in Tableau’s official resources12.

The initial calculation provided by the client likely overestimates the average number of orders per customer per month by region due to improper granularity control. The revised calculation must take into account both the customer and the region to correctly aggregate the data:

FIXED Level of Detail Expression: This calculation uses a FIXED expression to count distinct order IDs for each customer within each region. This ensures that the count of orders is correctly grouped by both customer ID and region, addressing potential duplication or misaggregation issues.

Accurate Aggregation: By specifying both [Customer ID] and [Region] in the FIXED expression, the calculation prevents the overcounting of orders that may appear if only customer ID was considered, especially when a customer could be ordering from multiple regions.

References:

Level of Detail Expressions in Tableau: These expressions allow you to specify the level of granularity you need for your calculations, independent of the visualization’s level of detail, thus offering precise control over data aggregation.