INCLUDE Level of Detail Expressions
INCLUDE level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view.
INCLUDE level of detail expressions can be useful when you want to calculate at a fine level of detail in the database and then re-aggregate and show at a coarser level of detail in your view. Fields based on INCLUDE level of detail expressions will change as you add or remove dimensions from the view.
Example 1
The following INCLUDE level of detail expression computes total sales per customer:
{ INCLUDE [Customer Name] : SUM([Sales]) }
When that calculation is placed on the Rows shelf, aggregated as AVG, and the [Region] dimension is placed on the Columns shelf, the view shows the average customer sales amount per region:
If the [Sales] measure is then dragged to the Rows shelf, the result illustrates the difference between the sum of sales (somewhere between $390K and $700K per region) and the average sales per customer (between $750 and $1100 per region):
Example 2
The following INCLUDE level of detail expression calculates sum of sales on a per-state basis:
{ INCLUDE [State] : SUM(Sales)}
Type the calculation directly on the Rows shelf, and then change the aggregation to AVG using the field's context menu. The resulting view averages the sum of sales by state across categories.
The view can be further enhanced by adding [Segment] to Columns and then copying the ad-hoc calculation from Rows to Label (using Ctrl+drag). In the image below we also dragged Segment to Color and then edited the colors for aesthetic effect. Now we can see how the average sum of sales per state varies across categories and segments.
