Level of Detail Expressions (LOD) enables a user to achieve deeper insights into data. Understanding LOD can be a bit tricky. In our previous blog, we focused on understanding the concept of ‘Include LOD’, its benefits and how to implement it in Tableau.
Let’s explore more on how to implement ‘Include LOD’ in Power BI and QlikSense using respective native functions by using the same scenario as explained in our previous blog.
To explain the functionality, we will utilize the below image. It shows the ‘Include LOD’ output for ‘Average Sales per customer’ for each state.
Finding Average Sales per customer in every state using ‘QlikSense ’
‘Aggr’ is a very powerful function in QlikSense. The function creates a temporary table with measureand dimensions. This functionality is similar to ‘Group By’ in SQL.
Syntax of the function is as follows:
Aggr(Aggregate Expression, Dimension 1, Dimension 2 … Dimension N)
- Create a table chart as shown below (In the below image average sales is computed for each ‘State’)
2. Add the following expression as a measure:
Avg(Aggr(Avg(Sales),State,[Customer ID]))
The Aggr() expression creates a temporary table in the backend with State, Customer ID and Avg(Sales). Therefore, the Avg(Sales) is computed at State and Customer ID level. The Avg(Sales) from the temporary table result is displayed in Image 3. ‘Average Sale per Customer ID’ for each state is now created.
Now you can see that the ‘Average Sale per customer ID’ values in Image 3 and tableau output Image 2 are the same.
We have now implemented ‘Include LOD’ in QlikSense using Aggr function.
Finding Average Sales per customer in every state using Power BI
- Build a Tabular chart as shown below
We can utilize Quick Measures feature in Power BI to generate DAX query for implementing LOD to get Average Sales per Customer for each state.
2. Right click on the Sales measure under fields menu and select Quick measures
3. Set the configurations as shown below in Image 5
As we are calculating the ‘Average Sales per Customer ID’, we select ‘per category’ option from the Calculation drop down. The aggregate we are using here is ‘average’ which is used as the base value. The category, in this case, is Customer ID. The Average Sales is now computed ‘per category’ (in our case Customer ID) in addition to the dimensions already present in the chart. Therefore, the ‘Average Sales per Customer ID’ for each state is calculated.
From the above image, we can find the DAX query generated for the configuration set under Quick measure. Under the fields menu, the created measure is listed. Using the measure in our chart we get the following output.
Comparing the values in Image 7 and Image 2 we can find that the values are the same. Therefore, we have implemented ‘Include LOD’ in Power BI to find ‘Average Sales per Customer’ in each state.
Learn more about our Microsoft Power BI offerings here.
