Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
194 Practical PowerPivot & DAX Formulas for Excel 2010
By overriding the current filter context, you can return figures for percentage of all
or percentage of parent. The next few formulas investigate how to override the filter
context.
The first two measures could also be written as follows:
=SUMX('Order Details','Order Details'[Sales Amount])
ALL() 2/7
The second and third measures have been changed. They now incorporate the
CALCULATE() function. CALCULATE() enables you to override the filter context—
although not in these examples.
ALL() is a Filter function. The examples are measures. Your figures may differ if you
have a different release of Northwind from the one used here.
Syntax
=SUM('Order Details'[Sales Amount])
=CALCULATE(SUM('Order Details'[Sales Amount]))
=SUM('Order Details'[Sales Amount])/
CALCULATE(SUM('Order Details'[Sales Amount]))
Result
Chapter 6: Fundamental Functions 195
Analysis
The introduction of the CALCULATE() function has had no effect on the pivot table.
The slicers still change the numerator and the denominator identically. CALCULATE()
by itself makes no difference. Its real power lies in its ability to accept parameters after
the expression to aggregate sales. These additional parameters are called the filter or
SetFilter parameters. One such parameter includes the ALL() function. The next
formula shows how to use it and what it does.
ALL() 3/7
The ALL() function in the denominator has a table as its own parameter. It can also
accept columns instead of a reference to a table.
ALL() is a Filter function. The examples are measures. Your figures may differ if you
have a different release of Northwind from the one used here.
Syntax
=SUM('Order Details'[Sales Amount])
=CALCULATE(SUM('Order Details'[Sales Amount]),ALL(Orders))
=SUM('Order Details'[Sales Amount])/
CALCULATE(SUM('Order Details'[Sales Amount]),ALL(Orders))
Result
196 Practical PowerPivot & DAX Formulas for Excel 2010
Analysis
To examine the purpose of ALL(), try slicing on Year and/or on Category. The
Category slicer affects both the numerator and the denominator. The Year slicer affects
only the numerator. The denominator continues to show the sales for all dates—
although that figure will vary by product category. The Year column is part of the
Orders table. The ALL() function is saying “consider all rows from the Orders table”;
consequently, all years are included in the calculation. The only filter that is operative is
that on product category, in this example. The ALL() function has overridden the Year
slicer in the denominator. The numerator is still affected by the Year slicer. In the result,
the sales of all categories made by Buchanan in 1998 were about 1.5 percent of the
sales of all categories by all employees for all time. The figures for each employee in the
denominator are the same, as the employee is part of the Orders table (it has a foreign
key of EmployeeID)—and the ALL() function is ignoring all context on that table.
ALL() 4/7
In these examples, the table used in the ALL() function has been changed from Orders
to Products.
ALL() is a Filter function. The examples are measures. Your figures may differ if you
have a different release of Northwind from the one used here.
Syntax
=SUM('Order Details'[Sales Amount])
=CALCULATE(SUM('Order Details'[Sales Amount]),ALL(Products))
=SUM('Order Details'[Sales Amount])/
CALCULATE(SUM('Order Details'[Sales Amount]),ALL(Products))
Chapter 6: Fundamental Functions 197
Result
Analysis
This time, the Category filter is ignored in the denominator, as it’s part of the Products
table—the table parameter in the ALL() function. The Year slicer, however, does affect
the denominator. In the result, Buchanan’s sales of Beverages were nearly 18 percent of
his sales of all categories. The ALL() function is invaluable for working out percentage
of parent or percentage of total. The figures for each employee in the denominator are
different here, as the employee is not part of the Products table.
ALL() 5/7
In these examples, there are two ALL() functions. Each takes a different table as a
parameter.
ALL() is a Filter function. The examples are measures. Your figures may differ if you
have a different release of Northwind from the one used here.
198 Practical PowerPivot & DAX Formulas for Excel 2010
Syntax
=SUM('Order Details'[Sales Amount])
=CALCULATE(SUM('Order Details'[Sales Amount]),ALL(Products),ALL(Orders))
=SUM('Order Details'[Sales Amount])/
CALCULATE(SUM('Order Details'[Sales Amount]),ALL(Products),ALL(Orders))
Result
Analysis
Neither of the two slicers has any effect on the denominator. Indeed, the employee
context is ignored too, as the employee is part of the Orders table. The denominator
is always going to be the same. However, the year, category, and employee all dictate
the context of the numerator. This is how to show a percentage of the grand total.
Buchanan’s sales of Beverages in 1998 were about 0.11 percent of the total sales of all
categories by all employees for all years.
ALL() 6/7
This is an important variation on the ALL() function. There is only the single ALL()
here, but it has the table that contains the source measure as one of its columns.
ALL() is a Filter function. The examples are measures. Your figures may differ if you
have a different release of Northwind from the one used here.
Chapter 6: Fundamental Functions 199
Syntax
=SUM('Order Details'[Sales Amount])
=CALCULATE(SUM('Order Details'[Sales Amount]),ALL('Order Details'))
=SUM('Order Details'[Sales Amount])/
CALCULATE(SUM('Order Details'[Sales Amount]),ALL('Order Details'))
Result
Analysis
Superficially, the result is identical to those in the previous examples. If you dig deeper,
there is a fundamental difference. The previous example ignored any context on the
Orders and the Products tables. However, you might add a column from another table
(to a slicer or to Report Filter, Column Labels, or Row Labels), and that could change
the filter context. Our example here will ignore any filters on Sales Amount, which is
part of the Order Details table. Such an approach can give the percentage of the grand
total, no matter how the end user adds table columns to the pivot table and slices and
filters. Unfortunately, we have run out of tables—so with Northwind it’s difficult to
prove! If you revert to the previous example and maybe filter on customer, it would
have no effect as the customer is part of the Orders table (as the CustomerID foreign
key). The same argument applies to the Categories table; there is a foreign key of
200 Practical PowerPivot & DAX Formulas for Excel 2010
CategoryID in the Products table. Maybe you might want to adapt this example and
the previous one to your own data—assuming you have more tables than we have here.
ALL() 7/7
To demonstrate these formulas, you may want to add a ProductName slicer (the column
is in the Products table). The ALL() function now has a column, rather than a table,
parameter.
ALL() is a Filter function. The examples are measures. Your figures may differ if you
have a different release of Northwind from the one used here.
Syntax
=SUM('Order Details'[Sales Amount])
=CALCULATE(SUM('Order Details'[Sales Amount]),
ALL(Products[ProductName]))
=SUM('Order Details'[Sales Amount])/
CALCULATE(SUM('Order Details'[Sales Amount]),ALL(Products[ProductName]))
Result
Chapter 6: Fundamental Functions 201
Analysis
There are a lot of interesting things going on here. The Category and ProductName
slicers are cascading—the selection in the former dictates the products shown in
the latter. If you select a product first, it automatically selects the right category and
makes the category the first one in the Category slicer. You can control this behavior
in the Slicer Settings dialog. If you then select a different category, you end up with an
empty pivot table as there is no relationship between the product and an inappropriate
category—you have to remove the filter in the products slicer.
From a DAX perspective, the important change is the column parameter to the
ALL() function. The Category slicer alters the denominator; the ProductName slicer
does not. The SetFilter argument is saying, “implement all filters except those on
product name.” The user can select a product category and the figures in the pivot table
will be updated, both numerator and denominator. When the user selects a product,
only the numerator gets updated. This technique is handy for showing percentage
of parent. In 1997, Buchanan’s sales of Chai were about 8.9 percent of his sales of all
Beverages.
ALLEXCEPT() 1/2
The ALLEXCEPT() function is another function that returns a table and can be used
as a SetFilter argument for the CALCULATE() function. Its own parameters are a
table name followed by one or more column names. Before we look at ALLEXCEPT(),
here are three formulas for a numerator, denominator, and a numerator divided by a
denominator.
ALLEXCEPT() is a Filter function. The examples are measures. Your figures may
differ if you have a different release of Northwind from the one used here.
Syntax
=SUM('Order Details'[Sales Amount])
=CALCULATE(SUM('Order Details'[Sales Amount]))
=SUM('Order Details'[Sales Amount])/
CALCULATE(SUM('Order Details'[Sales Amount]))
202 Practical PowerPivot & DAX Formulas for Excel 2010
Result
Analysis
All three slicers change the filter context. As you slice, the denominator is constantly
changing too. Please notice that if there are no sales for an employee, the default
behavior is to hide the employee.
ALLEXCEPT() 2/2
Now we introduce ALLEXCEPT() into the measures.
ALLEXCEPT() is a Filter function. The examples are measures. Your figures may
differ if you have a different release of Northwind from the one used here.
Syntax
=SUM('Order Details'[Sales Amount])
=CALCULATE(SUM('Order Details'[Sales Amount]),
ALLEXCEPT(Products,Products[Category]))
=SUM('Order Details'[Sales Amount])/
CALCULATE(SUM('Order Details'[Sales Amount]),
ALLEXCEPT(Products,Products[Category]))
Chapter 6: Fundamental Functions 203
Result
Analysis
The Category slicer affects the denominator; the ProductName slicer does not. The
ALLEXCEPT() syntax is asking to override all filters on the Products table, except
that on category. Another way of writing this is to use the ALL() function and then
a comma-separated list of all the columns in the Products table apart from Category.
ALLEXCEPT() is a convenient shorthand when you only want to respect the filter
context of one or two columns in a table, but override it on all the other columns. If
there are no sales for an employee, the employee is still shown—this is because the
denominator is never null.
ALLNOBLANKROW()
For these examples, the ProductName slicer has been removed.
ALLNOBLANKROW() is a Filter function. The examples are measures. Your figures
may differ if you have a different release of Northwind from the one used here.