Power BI DAX ALL Function – How It Works

I want to give you a really quick introduction to the Power BI DAX ALL function. I find that there can be a bit of confusion around how this function works, and I want to simplify it and make it easy to understand. This tutorial will enable you to recognize where and when you should use this function. You can watch the full video of this tutorial at the bottom of this blog.

The ALL function is really versatile and can enable some really high-level analytics if you truly understand what it actually does. I’m going to work through some examples so you can get a practical understanding of when to use it.

The ALL function is also known as the filter function or “remove context function”.

Table of Contents

Calculating Percent Of Sales Using The Power BI DAX ALL Function

I’ve got a really simple model here where I’m just analyzing my customer sales. I have a visualization as well as a table. And if I want to drill into a particular year, I can do that by using my slicer.

So I’m going to create a new measure and call it All Customer Sales. In this measure or formula, I’m going to use the CALCULATE function.

Understanding how CALCULATE works is crucial here. This function changes the context of a calculation. You can remove or add filters using CALCULATE. You can do a variety of things with it and you can branch out your measures.

Inside CALCULATE, I always input one of my core measures, and in this case, Total Sales. Next, I want to use ALL because it removes filters.

Then, I’m going to put my Customer Names column. I could put my Customer table here, but in this case, I’m just going to be specific. This will remove the Customer Names column from the context of this calculation.

When I input this measure into my table, you’ll see that it is giving us the total on every single row. If you look at the formula, I’m still calculating Total Sales, but I’m changing the content. I’m removing any filters that are coming from the Customer table. And that is why we now have the total in every single row.

And then from here, I can calculate the Percent (%) Of Customer Sales. All I need to do is just some simple measure branching. I simply DIVIDE Total Sales by ALL Customer Sales. And then I’m going to put a zero (0) as an alternative result.

I’m just going to turn this into a percentage, bring it to the table, and we see the percentage of sales from each customer.

Visualizing Techniques For More Insights

I can also turn this into a visualization for more meaningful insights. This can be relevant if you have a smaller customer base or a small number of products or if you want to compare your warehouse sales or different stores based on a percentage of total sales.

This is a really simple technique that you can utilize to enable that. And remember with all these calculations, you don’t actually need the intermediary calculations on the table. With measure branching, you can easily create tables like this.

Another thing that you can do, which I think works out quite well is Conditional formatting.

You can add data bars to your visualization and make it more compelling.

Power BI DAX ALL Function & Initial Context

The key thing to note here is to really understand what the initial context is. I’ll show you why.

For example, I’ll have my Products as my initial context instead of Customer Names. Check out what happens to all the numbers. They changed considerably and they don’t actually make sense.

Once you really understand what CALCULATE and ALL do in combination, you’ll see how they work with the initial context.

As you can see in this table, we have Products as the initial context and we have Total Sales. We are removing all the context from customers, but we’re not impacting the products at all because the Product Name is not in our formula. It’s actually doing nothing different to our Total Sales.

But if we layer our Customer Name on top of it, you’ll see that we have different amounts. This is because the ALL is now impacting the Customer Name here.

So we have some context from the Product Name and we have context from the Customer Names as well. We have the Total Sales for that combination, but in the All Customer Sales calculation, we are removing any context from the customer.

So if I sort this by Product Name, all of these numbers are the same for all these customers, because the context from the Customer is being removed with the ALL function.

And so if I scroll down, you’ll see that this number changes as soon as we go to a different product.

Conclusion

These are some of the key things that you really have to understand to effectively utilize the Power BI DAX ALL function.

Understand your current context of calculation, and remember that CALCULATE enables you to change that context. ALL is the remove filters function that you can place inside of CALCULATE.

So have a play around with this, and you’ll find many ways to use the ALL function and find lots of different insights. There is plenty of examples throughout Enterprise DNA‘s content on our platform so make sure you check them out as well.

All the best!

Sam

[youtube https://www.youtube.com/watch?v=8NVgLoSqMlM?rel=0&w=784&h=441]