Blogs / Microsoft / Power BI / Implementing LOD in Tableau, Power BI and QlikSense

Implementing LOD in Tableau, Power BI and QlikSense

May 20, 2019

SHARE

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.

Implementing LOD in Tableau, Power BI and QlikSense
Image 1: Implementing LOD in Tableau, Power BI and QlikSense

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)

  1. Create a table chart as shown below (In the below image average sales is computed for each ‘State’)

Implementing LOD in Tableau, Power BI and QlikSense
Image 2: Implementing LOD in Tableau, Power BI and QlikSense

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.

Implementing LOD in Tableau, Power BI and QlikSense
Image 3: Implementing LOD in Tableau, Power BI and QlikSense

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

  1. Build a Tabular chart as shown below

Implementing LOD in Tableau, Power BI and QlikSense
Image 4: Implementing LOD in Tableau, Power BI and QlikSense

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

Implementing LOD in Tableau, Power BI and QlikSense
Image 5: Implementing LOD in Tableau, Power BI and QlikSense

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.

Implementing LOD in Tableau, Power BI and QlikSense
Image 6: Implementing LOD in Tableau, Power BI and QlikSense

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.

Implementing LOD in Tableau, Power BI and QlikSense
Image 7: Implementing LOD in Tableau, Power BI and QlikSense

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. 


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!