Following our discussions on Fixed and Exclude LODs in earlier articles, you should now be fairly comfortable with these concepts. Therefore, tackling Include LOD should be relatively straightforward. Let’s dive in without further ado.

INCLUDE level of detail expressions calculate values by incorporating the dimensions specified, in addition to those already present in the view. This means you can include a dimension in the INCLUDE LOD for aggregation purposes, even if it’s not directly used in your visualization, allowing you to achieve the expected outcome. For those acquainted with SQL, this mechanism functions similarly to a ‘GROUP BY’ clause, with the flexibility that you don’t necessarily have to mention the ‘GROUP BY’ column in the select statement (though you have the option to do so). Moreover, it shares similarities with DAX iterator functions, offering a comparable level of functionality.

We have a bar chart with dual axis in Tableau and we’d like to replicate that in Power BI, there are two values, one is direct average aggregation on Sales column, the other one is a calculated column using Include LOD.

{ INCLUDE [Customer Name] : SUM([Sales]) }

So for each region, we are hoping to see average sales per customer and average sales in one visual. One way to achieve this goal is by using summarize function, it can effectively group Average Sales by Customer Name.

SalesPerCustomer = AVERAGEX(
    SUMMARIZE(
    Orders, Orders[Customer Name],"SumSalesPerCustomer", SUM(Orders[Sales])),
    [SumSalesPerCustomer])

Alternatively, we can also use GROUPBY instead of SUMMARIZE in this measure.

SalesPerCustomerGroupBy = AVERAGEX(
    GROUPBY(
    Orders, Orders[Customer Name],"SumSalesPerCustomer", SUMX(CURRENTGROUP(),Orders[Sales])),
    [SumSalesPerCustomer])

Word of caution: Even though GROUPBY and summarize perform SUMMARIZEoperations, they differs in performance, generally, rule of thumb is to Use GROUPBYfor grouping by dynamically-created columns in DAX, and SUMMARIZE for grouping by columns existing in your data model or DAX query. So in our case above, summarize would product the more efficient code.

That concludes our series of detailed illustration of how to replicate Tableau LOD in Power BI, hope you it helps with your report making and feel free to leave a comment if you have any question or suggestion.