# 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”.

## 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

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## How To Install DAX Studio & Tabular Editor In Power BI

In this tutorial, you'll learn how to download and install DAX Studio and Tabular Editor 3 in your...

## Creating A Date Table In Power BI The Fastest Way Possible

I'm going to show you a new approach in creating a date table in Power BI. You may watch the full video...

## Power BI Between Dates Working Hours Solution

In today’s blog, I will demonstrate an approach to Access Analytic’s Start Stop Challenge where, using...

## Building Your Data Model Relationships In Power BI

In today's tutorial, I want to highlight how important it is to build your data model relationships...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## Discover Unique Insights Using Power BI TOPN Function

In this video, I wanted to find an insight that was really unique, and hugely valuable for business....

## A New Look At CALCULATE – Power BI

In this tutorial, we'll be looking at how to use the DAX function CALCULATE in Power BI. You can watch...

## Ranking Visualization In Power BI – Dynamic Visual

In this blog post, I want to teach you how to show your customers moving through groups over time in a...

## Ranking Insights Using TOPN In Power BI

In this article, we will explore how we can use the TOPN function in Power BI. You will really enjoy...

## Data Storytelling And Important Questions To Ask

When it comes to data storytelling, there are three important factors to consider to make sure that our...