In a prior article, I illustrated and demonstrated the process of converting Tableau’s fixed level of detail (LOD) to Power BI. In this piece, I will extend this effort by guiding you through the translation of the EXCLUDE LOD to Power BI DAX.

Before diving into the examples, it’s crucial to review the workings of the EXCLUDE LOD. Unlike the FIXED LOD, which is typically employed for standalone calculations, EXCLUDE LOD often requires integration with other calculations for tasks such as percentage computation or trend analysis. It can also be applied as a color card or parameter, offering more flexibility and creative possibilities with EXCLUDE LOD.

Using our FIXED LOD example as a reference, we can obtain the total for each region by applying FIXED REGION. Interestingly, we can accomplish the same objective using EXCLUDE LOD. While FIXED instructs Tableau to aggregate data solely at the region level, disregarding any finer hierarchy, EXCLUDE explicitly directs Tableau to omit certain levels of aggregation, effectively achieving a similar outcome through a different approach.

What we had before Total Sales by Region using FIXED LOD

{ FIXED [Region]: SUM([Sales])}

Total Sales using EXCLUDE LOD

{ EXCLUDE [State/Province]: SUM([Sales])}

We are able to achieve the exact same result:

To replicate this logic in Power BI, we can employ functions like ALLEXCEPT or an alternative such as ALLSELECT. Both ALLEXCEPT and ALLSELECT function in a manner akin to FIXED and EXCLUDE, respectively, translating these LOD concepts into Power BI’s DAX language.

Total Sales by Region = 
CALCULATE(
    SUM(Orders[Sales]),
    ALLEXCEPT(Orders,Orders[Region])
)

Total Sales = 
CALCULATE(
    SUM(Orders[Sales]),
    ALLSELECTED(Orders,Orders[State/Province])
)

This represents a straightforward application of EXCLUDE. A more prevalent method involves utilizing it alongside a parameter. Adhering to the same framework, we aim to dynamically choose a category as a baseline and then compare the sales of other categories in terms of percentage.

We can start with something like this:

To enable dynamic selection, we need to create a parameter. For the allowable values, we select ‘list’ and add values from the field we intend to use, in this scenario, the subcategory.

To get a baseline value, we can use a simple if statement.

if [Sub-Category] = [Subcategory Selection] then [Sales] END

Drag and drop it onto the workbook, and we obtain the sum(sales) for the chosen category, exclusive to that category. However, to perform subtraction, division, and other calculations, we need these values to be available for all categories. How can we achieve this? By using the EXCLUDE LOD.

{ EXCLUDE [Sub-Category]: sum([Selected Subcategory Sales])}

With that, we can easily calculate the difference in percentage.

sum([Sales])/sum([EXCLUDE Sales])

Let’s bring subcategory parameter to the report, add color and do some sorting, we have this very nice report that not only ranks the sales by category and give us clear idea of where our selected subcategory stands.

Now let’s try to replicate the visual in Power BI. First, to mimic the parameter function in Power BI, we aim to create a parameter based on the values within a specific column. In the query editor, select the desired column, right-click it, and choose “Add as New Query.” If the column contains duplicate values, remove the duplicates.

Next, from the “Manage Parameters” feature and opt to create a new parameter. Select ‘Text’ as the type and for suggested values, utilize the query we’ve just established. Additionally, specify a default value for the selection. Following these steps and applying the changes, a parameter table will appear beneath our orders table. With this setup complete, we’re now ready to proceed with crafting an EXCLUDE-like measure using DAX.

Let’s drag subcategory, sales to a table and add a slicer using the parameter we just created.

Sales of Selected subCategory = 
 CALCULATE(
    SUM('Orders'[Sales]), 
        ALLSELECTED(Orders[Sub-Category]))

Observe that the sales for the chosen subcategory are populated across all categories due to our selection via the parameter slicer, resulting in the sum of sales being displayed only for that selected category. How can we get the sum of sales for each category individually? Recall we just discussed ALLEXCEPT, yes ALLEXCEPT can help us out here:

Sales of  subCategory = 
CALCULATE(
    SUM(Orders[Sales]),
    ALLEXCEPT(Orders,Orders[Sub-Category])
)

After that, we can add some conditional formatting to give a more appealing visual