Percentage Of Total Using ALL And ALLSELECTED

In this tutorial, I’ll discuss the difference between ALL and ALLSELECTED DAX functions when calculating the percentage of total in Power BI. The difference between these two DAX functions can be relatively confusing when you’re just starting out with Power BI. Hopefully, this tutorial can give you some clarity on this matter. You can watch the full video of this tutorial at the bottom of this blog.

I got this idea from a video that did an introduction about the ALL function. You can check that video from the Enterprise DNA Youtube Channel here.

In that video, the speaker compared the date versus the total sales using the ALL function. Here, I’m going to take that example one step further and show how to either use the ALL or ALLSELECTED function when calculating the percentage of total sales. This could be by date or by customer.

I’m going to use a Division example in this tutorial.

Basically, Division is like a job type.

I also placed a slicer at the top right part just to show that these results are from year 2020.

And this shows the Invoiced amount for each of the following Divisions.

I also provided a slicer for the Division that we’ll use later once we add the percentage of total invoiced using either the ALL or ALLSELECTED function.

Table of Contents

Invoiced Measure Using The ALL Function

This TREATAS Measures here is where I stored all my invoice measures.

The Invoiced measure is the first measure within my table.

This measure calculates the Invoiced amount, which is the Total Estimates.

I also used the TREATAS function because there’s no relationship between the Date table and the Jobs table, so I created that relationship virtually, instead.

And that’s how I created the Invoiced amount.

Now what I’ll do is to take the Invoiced using the ALL function.

This calculates the sum of all the amount Invoiced using the Invoiced measure that I previously discussed. I also used the ALL function to display all the results by Division in the Jobs table.

By adding the Invoiced ALL measure to this table, it only displays the total amount of invoice for each one of these rows.

So, that’s what the ALL function does. It returns all the rows in a table, or all the values of a column while ignoring any existing filter that might have been applied.

Percentage Of Total With ALL Function

After adding the Invoiced ALL measure to the table, the next thing that I want to do is to show the percentage of total sales for each one of these Divisions for the year of 2020.

To do that, I created another measure which I named as ALL Invoiced%. In this measure, I just divided the Invoiced measure by the Invoiced ALL measure.

Then, I’ll add that measure to the table. As you can see, it’s actually working correctly based on the results for Reconstruction Division. It shows that it has $775,766 out of $1,866,767, which makes sense for a percentage total of 41.56%.

But what if I only want to select a certain Division?

For example, I’ll use my slicer here so the table will only display the Reconstruction and the Mold Remediation divisions.

Noticeably, the ALL Invoiced% column is still displaying the same percentage.

It’s not showing the expected results that I want. This is because it’s basically just taking the Invoiced result divided by the Invoiced ALL result to get the percentage value.

What I want is to show the percentage of the Reconstruction and Mold Remediation out of the current total Invoiced amount.

This is where the ALLSELECTED function comes in.

Invoiced Measure Using The ALLSELECTED Function

I’ll unselect the Reconstruction and Mold Remediation selections for now. Then, let’s check out another measure that I created for Invoiced using the ALLSELECTED function. I named it Invoiced ALLSELECTED.

In this measure, I used the measure branching technique again. But instead of using the ALL function, I used the ALLSELECTED function.

I’ll add that measure again to the table. As you can see, the Invoiced ALLSELECTED column is showing the same amount as Invoiced ALL.

This is because by default, all the Divisions are selected in this model and I haven’t used the slicer yet.

Percentage Of Total With ALLSELECTED Function

I also created a measure named ALLSELECTED Invoiced% to get the percentage of total sales for each one of these Divisions for the year of 2020.

It’s similar to the ALL Invoiced% measure, but I used the ALLSELECTED function here instead of the ALL function.

Upon adding that to the table, you’ll see that it’s showing similar results from the ALL Invoiced% column.

However, here’s where the trick of this tutorial comes in. I’ll use the Division slicer again and select Reconstruction and Mold Remediation.

And you’ll see that the result of the ALLSELECTED Invoiced% column is now different from the ALL Invoiced% column.

The ALL Invoiced% column is only displaying 44.40%, because it’s still calculating the Invoiced amount of the other divisions even though they’re not selected.

On the other hand, the ALLSELECTED Invoiced% column where we used the ALLSELECTED function displays a 100% total. This is because it’s only calculating the Invoiced amount of the selected divisions.

This correctly shows that the Mold Remediation division makes 6% and the Reconstruction division makes up the 93% and a half of the $828,925 current total of Invoiced from both divisions.

To sum up, this is the difference between the ALL and the ALLSELECTED function. In this example, I’ll select more Division to further see the difference.

After selecting the Water Mitigation division, the numbers under the ALLSELECTED Invoiced% and ALL Invoiced% columns displayed a noticeable change.

Conclusion

That’s all I wanted to share in this tutorial. This valuable tip can definitely help you in calculating the correct percentage of total, whether it may be invoiced or total sales. Moreover, I hope this tutorial has given you the clarity on the difference between the ALL and ALLSELECTED functions in Power BI.

Check out the links below and our website as well for more examples and related content.

Jarrett