Percentage Of Total Using ALL And ALLSELECTED

Percentage Of Total Using ALL And ALLSELECTED

No comments

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 may 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 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.

percentage of total

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.

percentage of total

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

percentage of total

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.

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.

percentage of total

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 of 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.

percentage of total

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%.

percentage of total

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.

percentage of total

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

percentage of total

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.

percentage of total

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

percentage of total

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 as 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.

percentage of total

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

percentage of total

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

percentage of total

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.

percentage of total

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.

percentage of total

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.

percentage of total

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.

percentage of total

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

Membership Banne

***** Related Links *****
Calculate Percentage Margin In Power BI Using DAX
Calculating Dynamic Percentage Of Total Change Using Power BI Time Intelligence
Finding The Percent Of Total In Power BI

***** Related Course Modules *****

DAX Formula Patterns

DAX Formula Deep Dives
Power BI Super Users Workshop

***** Related Support Forum Posts *****
Issue Calculating Percentage Of Total
Calculate Percentages Of Subset And Total, Respecting Filters
Looking To Calculate The Percent Of Total In Power BI Using DAX
For more percentage of total support queries to review see here….

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.