Calculate Percentage In Power BI In Hierarchical Form

You might encounter a requirement where you’ll need to display the ratio percentage of a particular level in your data against a parent level. Many might find this to be a tricky task as there is no straightforward function to achieve this result. In this tutorial, I am going to demonstrate how you can calculate percentage in Power BI in hierarchical form. You can watch the full video of this tutorial at the bottom of this blog.

When we calculate the percentages for each line item by dividing Total Sales by the overall aggregated sales, we get the percentage of Total Sales for each line item.

But how do we calculate it when it is present in the form of a hierarchical feature that is created in the form of a subcategory? Let’s see how we can calculate the percentages in a hierarchical form.

How To Calculate Percentage In Power BI Hierarchically

In the matrix, we have created a hierarchy of rows. The first level of the hierarchy contains the territory from the Regions table whereas the second level of the hierarchy contains the product name from the Products table. We have the percentage of Total Sales for each territory, where the Total Sales is getting divided by the overall aggregated sales.

But when we move down to the next hierarchy, we’ll see that the percentage of Total Sales for each product is getting divided by the Total Sales of that particular category.

The Total Sales of Product 1 is getting divided by the overall Total Sales of New South Wales territory. The same goes for Product 2, and so on.

Now, let’s write the formula from scratch and evaluate it step by step.

Let’s call this measure % of Total Sales – 2. We’ll create a variable for Total Sales, which calculates the Total Sales for each individual line item, and this will return Total Sales.

Now we’ll create two variables. The first variable will help us calculate the overall Total Sales for all the territories. The second variable will help us calculate the overall Total Sales for all the products that are being sold inside that particular territory.

Let’s start with CALCULATE Total Sales and call out the ALLSELECTED function. Then we’ll reference the Regions table since all the territories come from the Regions table. Let’s use the same logic for the Product calculation as well. We’ll just change the variable name and the table reference as well.

So now, let’s call out these variables under the written statement. Let’s have the first variable.

As we can see, for each territory, we are getting the grand total result, which is as expected.

Let’s check out the results for the Products, which we should get per each individual territory for all the products.

Next, we’ll create the two sets of variables that will help us in calculating the percentage for Territory_Wise as well as for the Products_Wise.

We’ll use the DIVIDE function and divide our Total Sales by the Total Sales of territory, with the alternative result as zero. The same goes for the Products as well. Let’s just change the variable name and the variable reference.

Lastly, we will define our final variable, which will be the RESULTS variable. It will contain a SWITCH TRUE statement with the condition that if it’s a territory, then we want a result to be a Percentage of Territory; if it’s a product name, then we want the results to be a Percentage of Products_Wise.

Otherwise, we want a result to be as Percentage of Territory_Wise. We want to show our grand totals to be a hundred percent.

We will use a function called ISINSCOPE. This function retains a value in the form of true or false. Then, let’s bring the results under the written statement, and let’s format our results into percentage form.

As we can see, we are getting the expected results.

Finally, let’s see what happens when we change the order of our condition inside the SWITCH TRUE statement.

The results are actually not correct. The reason being is if we divide our 3 million Total Sales for product 1 by the 60 million Total Sales for New South Wales, we should be getting around 5 as a result, but rather we are seeing the results at 34%.

What happened here is that DAX calculates the innermost context first, and then moves towards the outermost context.

Therefore, in the case of hierarchies, our order of conditions also needs to be arranged accordingly.

In this case, our Product names are located at a lower level in the hierarchy, so they will go first as a condition. Since the territories are in the upper level or first level of the hierarchy, they will go last.

So, let’s change the order of our condition again to go back to normal. That will give us the correct results.

Conclusion

In today’s tutorial, I’ve shown you how to calculate percentage in Power BI in a hierarchical form. I’ve walked you through how it’s done by using a simple measure with some DAX functions and a SWITCH TRUE statement.

I hope you find this one helpful. You can watch the full video tutorial down the link below. Check out the related links as well for more similar content.

All the best!

Harsh

Understanding Data Models and Visualizations

Power BI is a robust and versatile data visualization tool that has gained popularity for its...

Getting Started with DAX in Power BI: A Beginner’s Guide

Data analysis expressions (DAX) are the key to unlocking the superpowers of Power BI. If you want to...

Building a Data Visualization Portfolio – Showcasing Your Skills and Insights

Data visualization is a powerful tool that allows you to communicate complex information in a way that...

Guide to Intermediate DAX Functions for Power BI

In today’s data-driven world, being able to use data analysis expressions (DAX) in Power BI and other...

Optimizing DAX: Performance Tips for Power BI Reports

Data analysis in Power BI is not only about creating visually appealing reports but also about ensuring...

DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.

Introduction to the RANKX and TOPN DAX functions in Power BI

One of the key aspects of analyzing data is to be able to rank or compare different elements based on...

Power BI Visualization Technique: Learn How To Create Background Design Plates

Here's a Power BI visualization technique that you can utilize within your reports by using large...

ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

Calculate Average Per Customer Transaction Using DAX In Power BI

What I want to demonstrate in this tutorial is how we can calculate average sales, profits, or...

Round Bar Chart – A Visualization For Your Dashboard

In this tutorial, you’ll learn how to create round bar charts for your Power BI report. It’s another...

Conditional Formatting with Transparency Hex Codes

One of the best ways to implement conditional formatting is using the Field value option in the...