Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
364 Practical PowerPivot & DAX Formulas for Excel 2010
Figure 12-18 Percentage of all
Figure 12-19 Percentage of column
Chapter 12: A Few Ideas: PowerPivot and DAX Solutions 365
Percentage of Parent
This time, a new measure has been added with the following formula:
=SUM('Order Details'[Quantity])/
CALCULATE(SUM('Order Details'[Quantity]),
ALLEXCEPT(Products,Products[Category]))
Figure 12-20 shows this new measure. The filter context for the denominator is now
the product category.
Customizing Measures for Each Row or Column
Often, you will want a value in the data area to behave differently depending upon the
current row or column filter context. Suppose, for example, that all sales are recorded in
U.S. dollars—apart from the U.K., where sales are recorded in pounds sterling. You may
Figure 12-20 Percentage of parent
366 Practical PowerPivot & DAX Formulas for Excel 2010
want to see the dollar value of U.K. sales. Figure 12-21 shows the result of adding the
following measure to the Order Details table, called DollarSales, with Country from
the Customers table on the rows:
=IF(COUNTROWS(VALUES(Customers[Country]))=1,
IF(VALUES(Customers[Country])="UK",
SUM('Order Details'[Sales Amount]) * 1.5,
SUM('Order Details'[Sales Amount])),
SUM('Order Details'[Sales Amount]))
Please note that DollarSales is different from Sales (=SUM('Order Details'
[Sales Amount])) for the U.K., and uses an exchange rate of 1.5 U.K. pounds for
one U.S. dollar. The IF(COUNTROWS(VALUES())) construct is necessary; otherwise,
the Grand Total row would throw an error. This is because VALUES() returns a table
of more than one row for Grand Total, and the IF() test requires a single row.
Figure 12-21 A measure customized for rows
Chapter 12: A Few Ideas: PowerPivot and DAX Solutions 367
Changes over Time
Another classic BI requirement is to show changes over time. For example, you may
wish to see increases and decreases in sales from one year to the next. The DAX Date
and Time functions are particularly useful in order to achieve such results. Some of
these functions require contiguous dates. Without contiguous dates, functions, such as
DATEADD() and SAMEPERIODLASTYEAR(), will either result in an error or produce
nonintuitive results. The example here uses ContosoRetailDW, as Northwind lacks
contiguous dates. You will need the DimDate, DimProduct, and FactSales tables.
Make sure that FactSales is related to both DimDate (on DateKey) and DimProduct
(on ProductKey). Add SalesAmount from FactSales to the Values drop-zone. Add
CalendarYear from DimDate to Column Labels and BrandName from DimProduct
to Row Labels. Interestingly, you will see that Adventure Works and Northwind supply
products to Contoso! The example that follows uses DATEADD() to retrieve sales
from the previous year and then compares the value to the current year to ascertain the
change in sales over time. You could also try PARALLELPERIOD(), if you wish. Here is
the DAX to work out a change in sales:
=SUM(FactSales[SalesAmount])-
CALCULATE(SUM(FactSales[SalesAmount]),DATEADD(DimDate[DateKey],-1,YEAR))
Moving Average
This example also uses ContosoRetailDW. The rows are from the DateKey in the
DimDate table, and the first column of values is the SalesAmount column from the
FactSales table. Figure 12-22 shows the result of a moving average measure. The formula
for the measure is:
=CALCULATE(SUM(FactSales[SalesAmount]),DATESBETWEEN(DimDate[Datekey],
FIRSTDATE(DATEADD(DimDate[Datekey],-2,DAY)),
LASTDATE(DimDate[Datekey])))/3
This is a three-day moving average. The divisor is 3, yet DATEADD() uses -2—that’s
going to return three dates including the current date. You can get rid of the first two
entries by testing for blanks. Northwind is not really suitable here as it lacks contiguous
dates.
368 Practical PowerPivot & DAX Formulas for Excel 2010
Running Total—Breaking
This example takes us back to the Northwind PowerPivot model. This is a Year-To-Date
example on the Sales Amount. The result is shown in Figure 12-23 and the formula is:
=TOTALYTD(SUM('Order Details'[Sales Amount]),Orders[OrderDate])
This is a breaking (or resetting) total as it starts again for August 1996.
Running Total—Non-Breaking
This running total has a different DAX formula:
=TOTALYTD(SUM('Order Details'[Sales Amount]),
Orders[OrderDate],ALL(Orders))
Figure 12-22 Moving average
Chapter 12: A Few Ideas: PowerPivot and DAX Solutions 369
The result in the last column in Figure 12-24 is different from the previous example.
This time, the total does not start again for August 1996. Also, some of the quarterly
and monthly totals are different. You should find that the yearly totals are identical to
the previous example (it’s using TOTALYTD()).
Figure 12-23 Running total—breaking
370 Practical PowerPivot & DAX Formulas for Excel 2010
Predefining Filters and Sets
One way to improve the end-user experience is to create filters and sets for them. A set
is an MDX term. A set is a defined list of values that you want to appear on Column
Labels and Row Labels. If you predefine filters and sets, the end user doesn’t have to use
Report Filter or slicers or other methods to see the required results.
Figure 12-24 Running total—non-breaking
Chapter 12: A Few Ideas: PowerPivot and DAX Solutions 371
Predefined Filter
This predefined filter in this example is a measure. It’s been added alongside Sales
Amount. The predefined filter is showing the sales for Davolio. Its formula is:
=CALCULATE(SUM('Order Details'[Sales Amount]),
Employees[LastName]="Davolio")
You can see the results in Figure 12-25. Please note that there is no Report Filter nor
slicer needed to set the filter context for the Employees table to Davolio.
Predefined Set
A predefined set functions pretty much like a table. It allows you to control which
elements are visible to the end user in a pivot table. Some people simply call them sets
or named sets. This is a new topic, so here are the step-by-step instructions:
1. Create a pivot table like the one in Figure 12-26. It’s using Category from the
Products table and Sales Amount from the Order Details table.
2. With the focus in the pivot table, click Fields, Items, & Sets in the Calculations
group of the PivotTable Tools/Options ribbon. This opens the drop-down menu
shown in Figure 12-27.
3. From this drop-down menu, choose Create Set Based on Row Items to open
the New Set dialog. This dialog is shown in Figure 12-28. In this dialog, you can
specify a name for your set and, optionally, a display folder. Without a display folder,
the set will appear under Sets in the PowerPivot Field List—it looks like a regular
table. With a display folder, the set will appear under a “table” with the folder name.
You can accomplish quite a lot in the New Set dialog. For example, you can delete
rows from the set. But here, we’re going to edit the MDX behind the set.
Figure 12-25 A predefined filter
372 Practical PowerPivot & DAX Formulas for Excel 2010
4. Click Edit MDX, and click OK to see the MDX behind the rows in the pivot
table. It’s reproduced here for clarity, as well as being shown in Figure 12-29.
{
([Products].[Category].&[Beverages]),
([Products].[Category].&[Condiments]),
([Products].[Category].&[Confections]),
([Products].[Category].&[Dairy Products]),
([Products].[Category].&[Grains/Cereals]),
([Products].[Category].&[Meat/Poultry]),
([Products].[Category].&[Produce]),
([Products].[Category].&[Seafood]),
([Products].[Category].[All])
}
Figure 12-27 Fields, Items, & Sets drop-down menu
Figure 12-26 Simple pivot table
Chapter 12: A Few Ideas: PowerPivot and DAX Solutions 373
Figure 12-28 New Set dialog
Figure 12-29 MDX for pivot table rows