What is LOD?

LOD (Level of Detail) allows you to compute values at the level of data source and visualization. However, it gives you more control over the level of granularity you want to compute. In FIXED LOD, we can specify the level of granularity. This blog is written for people who have the basics of Tableau but are beginners to Power BI.

I will use the Sample Superstore dataset to show how to use Tableau’s FIXED LOD in Power BI. If you want to do the same thing as me, you can download the data from here.

 

What’s the LOD like in Tableau?

Let’s see the below screenshot. Sales are drilled down at Sub-Category Level by default.

 

Figure 1. Sales at Sub-category Level in Tableau

 

How can we calculate the sum of sales at the Category level? We can use FIXED LOD to deal with that.

Create a calculated field and put the below statement:

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

Then you can get the following result.

 

Figure 2. Sales at Category Level by using LOD in Tableau

 

How to apply DAX to achieve the LOD in Tableau?

First, replicate the above process. Get the sum of sales at the Sub-category level.

 

Figure 3. Replication of Figure 1 in Power BI

 

To calculate the sum of sales at the Category level in Power BI, we can use ALLEXCEPT in DAX to do the same thing as FIXED LOD in Tableau. ALLEXCEPT removes all the context filters in the table except those that have been applied to the specified columns.

Right-click the Orders table and add a measure.

Write the below statement:

CALCULATE(
    SUM(Orders[Sales]),
    ALLEXCEPT(Orders,Orders[Category])
    )
Then you can get the same result.

Figure 4. Achieve LOD by DAX in Power BI

 

How to fix multiple dimensions in DAX?

In Power BI, you just need to add additional ALLEXCEPT functions as filters for CALCULATE to fix multiple dimensions.

Tableau Example:

{ FIXED [Category],[Product Name]:MAX([Order Date])}

 

Transform in Power BI:

CALCULATE(
    LASTDATE(Orders[Order Date]),
    ALLEXCEPT(Orders,Orders[Category],Orders[Product Name])
    )

 

If there are any problems, please feel free to point them out. Besides, you can reach out to me on LinkedIn. I will try my best to answer your questions about Tableau or Alteryx.

 

Joe Chan
Author: Joe Chan

Joe has an IT background with a master's degree in UNSW, majoring in AI and Data Science. During his studies, he realized Data is one of the most valuable assets a business can have and potentially has a tremendous impact on its long-term success. After graduation, his desire to level up his data analytics skills led him to join The Data School. He is interested in Data Wrangling, Data Visualization, and Machine Learning, eager to be a great Data Analyst to help businesses grow.