ALL Function
Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.
Term 
Definition 

table 
The table that you want to clear filters on. 
column 
The column that you want to clear filters on. 
The argument to the ALL function must be either a reference to a base table or a reference to a base column. You cannot use table expressions or column expressions with the ALL function.
This function is not used by itself, but serves as an intermediate function that can be used to change the set of results over which some other calculation is performed.
<Topic Status:> Some information in this topic is prerelease and subject to change in future releases. Prerelease information describes new features or changes to existing features in Microsoft SQL Server 2012 Service Pack 1 (SP1) Community Technology Preview 4 (CTP4). In cases where [Column] is marked as Date column by using Mark as Date table
As described in the following table, you can use the ALL and ALLEXCEPT functions in different scenarios.
Function and Usage 
Description 

ALL(Table) 
Removes all filters from the specified table. In effect, ALL(Table) returns all of the values in the table, removing any filters from the context that otherwise might have been applied. This function is useful when you are working with many levels of grouping, and want to create a calculation that creates a ratio of an aggregated value to the total value. The first example demonstrates this scenario. 
ALL (Column[, Column[, …]]) 
Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table. The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters. The second and third examples demonstrate this scenario. 
ALLEXCEPT(Table, Column1 [,Column2]...) 
Removes all context filters in the table except filters that are applied to the specified columns. This is a convenient shortcut for situations in which you want to remove the filters on many, but not all, columns in a table. 
Description
Assume that you want to find the amount of sales for the current cell, in your PivotTable, divided by the total sales for all resellers. To ensure that the denominator is the same regardless of how the PivotTable user might be filtering or grouping the data, you define a formula that uses ALL to create the correct grand total.
The following table shows the results when a new measure, All Reseller Sales Ratio, is created using the formula shown in the code section. To see how this works, add the field, CalendarYear, to the Row Labels area of the PivotTable, and add the field, ProductCategoryName, to the Column Labels area. Then, drag the measure, All Reseller Sales Ratio, to the Values area of the Pivot Table. To view the results as percentages, use the formatting features of Excel to apply a percentage number formatting to the cells that contains the measure.
All Reseller Sales 
Column Labels 





Row Labels 
Accessories 
Bikes 
Clothing 
Components 
Grand Total 
2005 
0.02% 
9.10% 
0.04% 
0.75% 
9.91% 
2006 
0.11% 
24.71% 
0.60% 
4.48% 
29.90% 
2007 
0.36% 
31.71% 
1.07% 
6.79% 
39.93% 
2008 
0.20% 
16.95% 
0.48% 
2.63% 
20.26% 
Grand Total 
0.70% 
82.47% 
2.18% 
14.65% 
100.00% 
Code
=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])
Comments
The formula is constructed as follows:

The numerator, SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]), is the sum of the values in ResellerSales_USD[SalesAmount_USD] for the current cell in the PivotTable, with context filters applied on CalendarYear and ProductCategoryName.

For the denominator, you start by specifying a table, ResellerSales_USD, and use the ALL function to remove all context filters on the table.

You then use the SUMX function to sum the values in the ResellerSales_USD[SalesAmount_USD] column. In other words, you get the sum of ResellerSales_USD[SalesAmount_USD] for all resellers sales.
For more information about creating measures, see Create a Measure in a PivotTable or PivotChart.
Note 

The above example uses the tables, ResellerSales_USD, DateTime, and ProductCategory from the DAX sample workbook. For more information about samples, see Get Sample Data . 
Description
Assume that you want to create a table showing the percentage of sales compared over the years for each product category (ProductCategoryName). To obtain the percentage for each year over each value of ProductCategoryName, you need to divide the sum of sales for that particular year and product category by the sum of sales for the same product category over all years. In other words, you want to keep the filter on ProductCategoryName but remove the filter on the year when calculating the denominator of the percentage.
The following table shows the results when a new measure, Reseller Sales Year, is created using the formula shown in the code section. To see how this works, add the field, CalendarYear, to the Row Labels area of the PivotTable, and add the field, ProductCategoryName, to the Column Labels area. To view the results as percentages, use Excel's formatting features to apply a percentage number format to the cells containing the measure, Reseller Sales Year.
Reseller Sales Year 
Column Labels 





Row Labels 
Accessories 
Bikes 
Clothing 
Components 
Grand Total 
2005 
3.48% 
11.03% 
1.91% 
5.12% 
9.91% 
2006 
16.21% 
29.96% 
27.29% 
30.59% 
29.90% 
2007 
51.62% 
38.45% 
48.86% 
46.36% 
39.93% 
2008 
28.69% 
20.56% 
21.95% 
17.92% 
20.26% 
Grand Total 
100.00% 
100.00% 
100.00% 
100.00% 
100.00% 
Code
=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/CALCULATE( SUM( ResellerSales_USD[SalesAmount_USD]), ALL(DateTime[CalendarYear]))
Comments
The formula is constructed as follows:

The numerator, SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]), is the sum of the values in ResellerSales_USD[SalesAmount_USD] for the current cell in the pivot table, with context filters applied on the columns CalendarYear and ProductCategoryName.

For the denominator, you remove the existing filter on CalendarYear by using the ALL(Column) function. This calculates the sum over the remaining rows on the ResellerSales_USD table, after applying the existing context filters from the column labels. The net effect is that for the denominator the sum is calculated over the selected ProductCategoryName (the implied context filter) and for all values in Year.
For more information about creating measures, see Create a Measure in a PivotTable or PivotChart.
Note 

This example uses the tables, ResellerSales_USD, DateTime, and ProductCategory from the DAX sample workbook. For more information about samples, see Get Sample Data . 
Description
Assume that you want to create a table that shows the percentage of sales for each product category, on a yearbyyear basis. To obtain the percentage for each product category in a particular year, you need to calculate the sum of sales for that particular product category (ProductCategoryName) in year n, and then divide the resulting value by the sum of sales for the year n over all product categories. In other words, you want to keep the filter on year but remove the filter on ProductCategoryName when calculating the denominator of the percentage.
The following table shows the results when a new measure, Reseller Sales CategoryName, is created using the formula shown in the code section. To see how this works, add the field, CalendarYear to the Row Labels area of the PivotTable, and add the field, ProductCategoryName, to the Column Labels area. Then add the new measure to the Values area of the PivotTable. To view the results as percentages, use Excel's formatting features to apply a percentage number format to the cells that contain the new measure, Reseller Sales CategoryName.
Reseller Sales CategoryName 
Column Labels 





Row Labels 
Accessories 
Bikes 
Clothing 
Components 
Grand Total 
2005 
0.25% 
91.76% 
0.42% 
7.57% 
100.00% 
2006 
0.38% 
82.64% 
1.99% 
14.99% 
100.00% 
2007 
0.90% 
79.42% 
2.67% 
17.01% 
100.00% 
2008 
0.99% 
83.69% 
2.37% 
12.96% 
100.00% 
Grand Total 
0.70% 
82.47% 
2.18% 
14.65% 
100.00% 
Code
=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/CALCULATE( SUM( ResellerSales_USD[SalesAmount_USD]), ALL(ProductCategory[ProductCategoryName]))
Comments
The formula is constructed as follows:

The numerator, SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]), is the sum of the values in ResellerSales_USD[SalesAmount_USD] for the current cell in the PivotTable, with context filters applied on the fields, CalendarYear and ProductCategoryName.

For the denominator, you use the function, ALL(Column), to remove the filter on ProductCategoryName and calculate the sum over the remaining rows on the ResellerSales_USD table, after applying the existing context filters from the row labels. The net effect is that, for the denominator, the sum is calculated over the selected Year (the implied context filter) and for all values of ProductCategoryName.
For more information about creating measures, see Create a Measure in a PivotTable or PivotChart.
Note 

This example uses the tables, ResellerSales_USD, DateTime, and ProductCategory from the DAX sample workbook. For more information about samples, see Get Sample Data. 