In different data models, you may have seen hierarchical data. In DAX, we don’t have any straightforward function to calculate over hierarchies.
However, there are different ways to handle this type of requirement. For example, you need to display the ratio percentage of each level against its parent level.
In this blog, I am providing all those DAX steps to help you solve this problem.
At first import, the US Super Store data from Kaggle and save it to Power BI Desktop.
- Let’s start with the Get Data option under the Home tab. As this is a CSV file, select the Text/CSV option from the drop-down list
- Select the file named US Superstore data.csv
- After selecting the file, data will be displayed in the below format
- Click on Load and save data.
I was going through the different books on DAX.
From one of the book (Definitive Guide to DAX, The: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel, 2nd Edition) written by By Alberto Ferrari and Marco Russo, I found the below details.
“There is no way of building a generic ratio to parent measure that works on any arbitrary combination of columns in a report.
The reason is that inside DAX, there is no way of knowing how the report was created or how the hierarchy was used in the client tool. DAX has no knowledge of the way a user builds a report.
It receives a DAX query; the query does not contain information about what is on the rows, what is on the columns, or what slicers were used to build the report.”
At this point, we know that there is no easy process, so we can proceed with measure creations which can help to create a ratio to parent level data.
Create 3 calculated Measures
- To start with creating basic calculated measure Total Sales
Total Sales = SUM(‘US Superstore data’[Sales])
2. In this data set, there are 3 levels in product hierarchy, Category, Sub-Category and Product Name.
3. If you want to calculate the percentage for each level, then you have to create some measures. Before moving to create a single measure, first, we will try with 3 individual measures for each level.
In the above measures, we have used ALLSELECTED() DAX function. According to Microsoft document,
“The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters.
This function can be used to obtain visual totals in queries.”
In short, you can say, this function is mainly for filtering data when we are going to create total calculation in the visuals.
4. Create Product Hierarchy (Category → Sub-Category -> Product Name)
5. Select Matrix Visual from the Visualization section
6. Add Product Hierarchy, Total Sales and other newly created measures to the visual.
You can observe that some of the area measures are displaying correct relevant values, but most of the cases displaying 100% which is useless to us.
Even, to display this type of report, we should think about creating one measure instead of 3 measures.
Let’s find out how we can solve this issue.
Create Single Calculated Measure
- Create the below measure with DAX variable condition
2. In the above measure, we have used another new DAX function ISINSCOPE() which is under DAX Information Function category.
DAX information functions look at the cell or row that is provided as an argument and tells you whether the value matches the expected type.
ISINSCOPE returns TRUE when the specified column passed as an argument and is filtered at a level of hierarchy levels. Finally it performs the grouping.
Note : This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
3. Create similar report like above and see the difference.
Please find the code in the below location
DAX — Chapter 8 https://github.com/arpitag1/Power-BI
In this blog, we learn how to calculate percentage over hierarchies using DAX functions.
If you have any questions related to this project, please feel free to post your comments.
Please visit my website for other technical resources.
Please like, comment and subscribe to my YouTube channel which you have already seen. 🙂 Keep Learning.