Tennick A. Practical PowerPivot & DAX Formulas for Excel 2010
Подождите немного. Документ загружается.
354 Practical PowerPivot & DAX Formulas for Excel 2010
If you have the ContosoRetailDW SQL Server sample database, you don’t have to
do this—the primary and foreign keys are already dates.
Sorting on Dates
Sorting on dates, especially months, is not always an out-of-the-box solution. If you
add month numbers to Column Labels or Row Labels or to a slicer, they should sort
correctly. However, your users will likely prefer to see month names. Naturally enough,
these are going to sort alphabetically—which is not what you want. The problem is
illustrated in Figure 12-8 for Row Labels, and in Figure 12-9 for a slicer.
Figure 12-4 Define Time Periods dialog
Chapter 12: A Few Ideas: PowerPivot and DAX Solutions 355
Neither looks that good. The solution to the problem depends on whether it’s
Column Labels/Row Labels or a slicer. Here are some simple steps to sort month
names correctly in either Column Labels or Row Labels:
1. Add Sales Amount to Values, and Year, Quarter, and MonthName (the formulas
for these date parts are at the start of this chapter) to Row Labels.
2. Right-click on any month name. Choose Sort followed by Sort A to Z. This does
not sort alphabetically! When you choose this option, Excel’s own intelligent
sorting algorithm takes over. For this to work you must use standard names
for the months, for example, Jan or January—FirstMonth as a name would not
work. The pivot table, shown in Figure 12-10 now has the month names sorting
correctly.
Figure 12-5 Select Calendars dialog
356 Practical PowerPivot & DAX Formulas for Excel 2010
Figure 12-6 Schema Generation Progress dialog
Figure 12-7 Generated time dimension in PowerPivot
Chapter 12: A Few Ideas: PowerPivot and DAX Solutions 357
Figure 12-8 Month names in Row Labels
Figure 12-9 Month names in a slicer
Unfortunately, the previous steps do not apply to slicers. For a slicer, you have to
prepend the month name with the month number. This is not perfect, and will not be
necessary in the next release of PowerPivot. But for now, here are the steps to sort on
month name correctly in a slicer:
1. Add a calculated column to the Orders table and enter the following DAX formula:
=IF(LEN([Month]) = 1,"0" & [Month] & " - " & [MonthName],
[Month] & " - " & [MonthName])
2. Use this calculated column in the slicer. The result is shown in Figure 12-11.
Please note that the zero in front of single-digit month numbers is necessary for
a correct sort.
358 Practical PowerPivot & DAX Formulas for Excel 2010
Figure 12-10 Month names sorting correctly in Row Labels
Figure 12-11 Month names sorting correctly in a slicer
Chapter 12: A Few Ideas: PowerPivot and DAX Solutions 359
Numbers That Don’t Add Up
Many numbers (whether from calculated columns or calculated measures) can be
aggregated in all situations. In our Northwind PowerPivot model, such numbers
are Quantity and Sales Amount from the Order Details table. It makes sense to see
subtotals and totals for these numbers, whether by product or employee or customer or
date. Such numbers are full-additive numbers. Some numbers, however, can never be
usefully aggregated or only aggregated in certain circumstances. The former are called
non-additive numbers and the latter, semi-additive numbers. A semi-additive number
might be a bank balance. If you have two bank accounts, you can add them together to
give you your total balance. Unfortunately, you can’t add them together solely by time! If
you had $100 in your account last week and $200 this week, sadly, you don’t necessarily
have $300. Bank balances are only additive in certain circumstances. A non-additive
number might be an inventory figure. If you have 10 of the product Chai in stock and
20 of the product Chang, it might not be meaningful to say you had 30 in stock. That is
a business decision; you could argue that to say you had 30 products in stock was sensible.
To take another example, if you had 10 Chai last week and 5 Chai this week, the answer
is probably not 15. Maybe the answer is 5, the last non-blank value (there is a DAX
function called LASTNONBLANK(), which is very useful when looking at totals across
time). This section deals with non-additive and semi-additive numbers in a pivot table.
Take a look at Figure 12-12. The pivot table is filtered to show only a few products.
You might conclude that the grand total for UnitsInStock (from the Products table) is
not a valid total. What if you would prefer to hide this total?
You can easily hide the grand total for UnitsInStock by going through the Grand
Totals button in the Layout group of the PivotTable Tools/Design ribbon. However,
that is also going to hide the total for Sales Amount, which is perfectly valid and may
be required by end users. Here’s an approach that can be used to selectively hide totals.
Add a new measure to the pivot table with the following formula:
=IF(COUNTROWS(VALUES(Products[ProductName]))=1,SUM(Products[UnitsInStock]))
Figure 12-12 Incorrect total for inventory?
360 Practical PowerPivot & DAX Formulas for Excel 2010
The formula is counting the number of rows in the current filter context for the
product name. If the number of rows is 1, then the current context is at the individual
product level. The third parameter for the IF() function is missing. That’s the false
result, and if it’s missing, it returns a blank—or you could explicitly use BLANK(). The
result is shown in Figure 12-13.
An interesting problem arises when you aggregate UnitsInStock across time. Here, you
may want to see a total. The most meaningful total for a product would be equal to the
last stock-take figure for that product. In SSAS, there are two useful aggregations called
LastChild and LastNonEmpty for working with inventory figures. Fortunately, there is
a DAX equivalent of LastNonEmpty, the DAX function LASTNONBLANK(). In order to
demonstrate this, we need inventory figures across time. The Northwind UnitsInStock
column is a static picture, and the stock-take is not dated. However, if you have the SQL
Server sample database, ContosoRetailDW (you can download it from www.microsoft.
com—search on ContosoRetailDW), you can look at inventory figures across time—
you’ll need the DimDate and FactInventory tables. The practical shows how to handle
numbers that do not add up over time. Here are the step-by-step instructions:
1. Add the DimDate, DimProduct, and FactInventory tables from ContosoRetailDW
to a PowerPivot model. DimDate contains dates, and FactInventory includes
stock-take figures for different dates and products. DimProduct is added so you
can see the product name, rather than a product key. You may need to verify the
relationships between these tables. FactInventory should be related to DimDate
through the DateKey column. It should also be related to DimProduct through
the ProductKey column. When you add tables separately, the relationships in the
source data are not discovered by PowerPivot.
2. Insert a pivot table and add CalendarYear (from DimDate) to Column Labels,
ProductName (from DimProduct) to Row Labels, and OnHandQuantity
(from FactInventory) to Values. Set a label filter on the rows to adventure
works laptop12, using Label Filters, Begins With (and I thought Adventure
Works only made bikes!)—the result is shown in Figure 12-14.
Figure 12-13 Correct total for inventory?
Chapter 12: A Few Ideas: PowerPivot and DAX Solutions 361
3. The Grand Total for each row is incorrect. It should be equal to the last annual
stock-take figure, and not a summation. To start fixing the problem, replace the
entry for OnHandQuantity in Values with a new measure called BadInventory.
You may want to add this to the FactInventory table. The formula follows:
=SUM(FactInventory[OnHandQuantity])
4. The totals are still incorrect, and there is no figure for M1200 Black for 2009.
Remove BadInventory from the Values drop-zone (but not from the field list),
and add another measure, called Inventory. The formula is:
=SUMX(VALUES(DimProduct[ProductKey]),CALCULATE([BadInventory],
LASTNONBLANK(DimDate[Datekey],[BadInventory])))
is is a reasonably complex piece of DAX. It means “sum all the values in the
DimProduct table, but only using the last inventory figure found for each date.”
e result is shown in Figure 12-15. e Grand Total for each row is now correct.
e inventory figures for each year also look better—they are now using the last
stock-take figure in each year, rather than a sum.
Figure 12-14 An incorrect inventory pivot table
Figure 12-15 A better inventory pivot table
362 Practical PowerPivot & DAX Formulas for Excel 2010
5. However, there is still a problem. The figure for M1200 Black is missing for 2009.
If this entire product had been sold in 2009, the figure would presumably be zero.
A blank, on the other hand, means that no stock-take was performed in 2009.
In that case, it might be reasonable to assume that the 2009 figure should be the
same as the 2008 figure (implying that there were no sales from stocks in 2009).
Change the DAX formula to this:
=SUMX(VALUES(DimProduct[ProductKey]),CALCULATE([BadInventory],
LASTNONBLANK(DATESBETWEEN(DimDate[Datekey],
BLANK(),
LASTDATE(DimDate[Datekey])),[BadInventory])))
The final result is shown in Figure 12-16. There is now an entry for M1200 Black
for 2009. In addition, we now have the stock figures for 2010 and 2011. The correct
answers are being returned. The DATESBETWEEN() function looks at all dates up to
the last, or current context, date from the beginning of time. The last date is given by
LASTDATE(). LASTNONBLANK() then finds the last stock-take figure within the date
range returned by DATESBETWEEN().
Classic BI Solutions
This section considers some classic BI solutions. The ones examined are percentage of
all, percentage of a row or column total, percentage of parent, a measure customized
for a row or column context, changes over time, a moving average, and two forms of
a running total. You can build a lot of these by using the pivot table GUI, but a DAX
solution gives you a lot more control over the filter context.
Percentage of All
Our starting point is shown in Figure 12-17. It’s using the Northwind PowerPivot
model (Category, ProductName, Year, and Quantity).
Figure 12-16 A correct inventory pivot table
Chapter 12: A Few Ideas: PowerPivot and DAX Solutions 363
To work out the percentage of all, add a new measure to the Order Details table. The
formula is:
=SUM('Order Details'[Quantity])/
CALCULATE(SUM('Order Details'[Quantity]),ALL('Order Details'))
This returns the percentage of all because the filter context for the denominator
is always the total quantity—ALL('Order Details'). The result is shown in
Figure 12-18; the figures are formatted as percentages (right-click, Number Format).
Percentage of Column or Row Total
Figure 12-19 shows the addition of a percentage of column total measure. The DAX
formula for this is:
=SUM('Order Details'[Quantity])/
CALCULATE(SUM('Order Details'[Quantity]),ALL(Products))
This uses ALL(Products) rather than ALL('Order Details').
Figure 12-17 A Northwind pivot table