ALLSELECTED() function has a reputation of an extremely complex function in DAX and I’ve spent quite a bit of time trying to get my head around it.

In this blog post, I will cover only a tiny part of how ALLSELECTED() acts, and I definitely recommend you dive deeper into it to understand its different behavior patterns. Here is a great article I found that covers ALLSELECTED() on a bit more than 20 pages: The definitive guide to ALLSELECTED – SQLBI

Because it is a brief introduction to ALLSELECTED() function I won’t be covering its behavior as a Table, neither I will go into details of shadow filter context. The three things I will be looking at are:

  1. Definitions of ALLSELECTED() function and ALL() function. ALLSELECTED() as a CALCULATE() modifier
  2. What values ALLSELECTED() returns for each row, based on the parameters used
  3. How to calculate the Percent of Total with ALLSELECTED() vs ALL()

I use a single table “DEMO” in my examples, where:

Sales measure = SUMX(DEMO, DEMO[Sales])

 

Definitions of ALL() and ALLSELECTED() functions and what do they actually mean

Let’s start with the definition.

ALLSELECTED() returns all rows in a table or values in a column, ignoring filters specified in the query but keeping any other existing filter context. The output of this function is a table.

ALLSELECTED() accepts the following parameters:

  1. Name of the Table you want to remove filters from
  2. Name of the Column or Columns you want to remove filters from. If you use multiple columns, they must belong to the same referenced table
  3. No Input, if you use ALLSELECTED() as a CALCULATE() modifier

ALLSELECTED() can be used as a Table function and as a CALCULATE() modifier

In the below formula I do not specify a table or a column to remove filters from, we are leaving it up to the context of the report.

Sales ALLSELECTED = CALCULATE([Sales measure], ALLSELECTED())

 

What we see from the above table is that all row and column filters are ignored, and we are getting the same repeating total for each row for “Sales ALLSELECTED”. Why is that? Because there is nothing else ALLSELECTED() can look at to change the filter context. And, as we know, from the definition, ALLSELECTED() “returns all rows in a table or values in a column, ignoring filters specified in the query” which we left up to our visual and obviously ALLSELECTED() is ignoring Dept, Person and Occupation context, all the filters within the visual.

Introduction to ALL() function

As its name suggests, ALL() returns all the rows in a table, or all the values in a column. ALL function removes the applied filters from the filter context.

The biggest difference between ALL() and ALLSELECTED() is that the ALL function ignores all filters, regardless of where they are coming from.

Total Sales ALL = CALCULATE([Sales measure], ALL(DEMO))

When all Departments are selected in the slicer, we don’t see any difference in the results of these two functions.

But what happens when we introduce the external filter, our Dept slicer?

 

’’Sales ALLSELECTED’’ has recalculated its totals based on our slicer selection. And why is that?  Because from the definition ALLSELECTED() “returns all rows in a table or values in a column, ignoring filters specified in the query but keeping any other existing filter context”. Our slicer is an external filter which ALLSELECTED() takes into consideration.

On the other hand, ALL() is ignoring all filters, including the external ones.

‘’Sales ALLSELECTED’’ is usually used to obtain Visual Total or Subtotals in a query. Visual total is the Total shown on the visual instead of the Total in the dataset.

“Total Sales ALL” is returning the Total of the dataset.

Another way to think about it is ALLSELECTED() restores visible values to the filter context. The visible values in our slicer are: Finance and HR, that’s why “Sales ALLSELECTED” restores them back into the filter context and we are getting the total value only for those two departments.

 

 ALLSELECTED () and its parameters

Let’s expand our table and add the following calculations to check the influence of the parameters in ALLSELECTED () – one column, two columns and a table – on the values breakdown.

Total Sales ALLSELECTED Dept = CALCULATE(SUM(DEMO[Sales]), ALLSELECTED(DEMO[Dept]))

Total Sales ALLSELECTED Person = CALCULATE(SUM(DEMO[Sales]), ALLSELECTED(DEMO[Person]))

Total Sales ALLSELECTED Dept&Person = CALCULATE(SUM(DEMO[Sales]), ALLSELECTED(DEMO[Dept], DEMO[Person]))

Total Sales ALLSELECTED Table = CALCULATE(SUM(DEMO[Sales]), ALLSELECTED(DEMO))

 

 

We can see straight away that the column values for “Sales ALLSELECTED” and “Total Sales ALLSELECTED Table” are the same. And this is because both calculations ignore all the filters within the visual.

The results where the columns have been used as parameters are a bit different.

When we have the Dept column as our parameter the values are filtered by Person and Occupation filter context, but not by Dept.

Person parameter is ignoring Person filter context and filtering only by Dept and Occupation. That’s why for HR we see slightly different totals – Brad and Rey are getting 500 each because they both work in HR and they have the same Occupation. So, their amount sums up and is not broken by the names (Person filter is ignored).

A parameter with two columns Dept and Person ignores those two columns as a filter and filters only on Occupation. That’s why every row has the total amount based on the occupation only.

When we use an external filter, our Dept slicer, we see the same pattern, but now our totals are based on the Departments selected in the slicer.

It is important to understand which parameters to use in a formula, because, although the Total is the same, but the breakdown of the values inside the visual will be different.

Percent of Total Calculations

If we want to calculate the Percent of Total, we need to clearly understand what we are trying to achieve.

Do we want to see the percent of sales for the department based on the overall amount of sales for all the departments (which is our dataset total)

Or

Do we want to see the percent of sales for the department based on the total of the selected departments (which is our visual total)

In the first case our calculation will be:

% of Total = DIVIDE([Sales measure], [Total Sales ALL])

In the second case we will use different calculation:

% of Total based on Slicer = DIVIDE([Sales measure], CALCULATE([Sales measure], ALLSELECTED(DEMO)))

 Or

% of Total based on Slicer = DIVIDE([Sales measure], [Total Sales ALLSELECTED Table])

 

 

 

While both calculations total up to 100% when all departments are selected, the picture changes when we use the slicer.

“% of Total ALL” tells us that Finance and HR give 64.29% of our total sales ($1400), where Finance contributes 21.43% and HR contributes 42.89%.

“% of Total based on Slicer” gives us 100% total value because those selected departments’ percentage is based on the sales amount only for those two selected departments ($900 visual total). Which means that to the combined sales of Finance and HR departments of 100%, Finance department contributes 33.33% and HR contributes 66.67%.

 

Two important conclusions:

  1. It does matter which parameter you use for ALLSELECTED() function. Although the total will be the same but the breakdown inside the matrix will depend on the parameter you use.
  2. ALL() and ALLSELECTED() treat filter context differently.
  3. The calculations for Percent of total are different, depending on what results you are trying to achieve. We can calculate Percent of total based on the dataset total or Percent of total based on the slicer selected total (visual total)
The Data School
Author: The Data School