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

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

## MultiIndex In Pandas For Multi-level Or Hierarchical Data

MultiIndex in Pandas is a multi-level or hierarchical object that allows you to select more than...

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

## Sales Vs Budgets Insights â€“ Extended Budget Allocation Formula

In this tutorial, Iâ€™m going to show you an extended version of the ultimate Budget Allocation...

This blog will show you some of the best tools and tricks to quickly create excellent visuals for your...

## Power BI Percent Of Total – Using CALCULATE Statement

Power BI percent of the total is a really common calculation that we require quite often. This is great...

## Power Query Data Types And Connectors

This tutorial will talk about Power Query data types and connectors. You'll learn what data types are...

## Power BI Custom Calendars: Calculating For Month On Month Change – 445 Calendars

This tutorial is about how you can run time intelligence calculations over custom calendars in Power...

## DAX CALCULATETABLE Vs FILTER Function

Both CALCULATETABLE and FILTER are powerful tools for manipulating and analyzing data in DAX query, but...

## Setting Up A Dynamic StartDate And EndDate For Power Query Date Tables

I'll show you how to set up dynamic start dates and end dates using Power BIâ€™s power query date...

## Bookmarks In Power BI – Grouping by Report Page

I'm going to show you how I utilize bookmarks in Power BI. For example, if I have multiple bookmarks...

## Power Query Features: An Overview

This tutorial will discuss about the available features inside the Power Query Editor. You'll learn how...

## Creating A Master Reporting Planner For Power BI Deployment

In this tutorial, youâ€™ll learn how to create a Master Reporting Planner in the Analyst Hub to organize...