4 Practical PowerPivot & DAX Formulas for Excel 2010
T
his is a short “quick start” chapter. It shows just how easy it is to create business
intelligence (BI) with PowerPivot for Excel. Essentially a practical chapter, it
does not contain much by way of theory or concepts—these are introduced
gradually throughout the rest of the book. The chapter shows how to build quite a
sophisticated pivot table in just a few minutes.
C
Key concepts Quick introduction to PowerPivot, quick introduction to Data
Analysis eXpressions (DAX), importing data, creating calculated columns and
measures, creating a PowerPivot pivot table in Excel
This is real business intelligence—it’s a PowerPivot pivot table in an Excel 2010
worksheet, as shown in Figure 1-1—and, with a little practice, you can build this pivot
table in less than five minutes!
Let’s try building this business intelligence right now, and learn a little about
PowerPivot for Excel and Data Analysis eXpressions (DAX) formulas.
The example here uses data from the SQL Server Northwind sample database. Of
course, you may not have SQL Server—and if you do, you may not have the Northwind
database! Fortunately, there are lots of alternatives and solutions to this.
C
If you don’t have SQL Server, you can download an Evaluation Edition from
www.microsoft.com.
C
If you have SQL Server, but not the Northwind sample database, instructions on
how to download Northwind are in the introduction.
C
What if you don’t want to (or can’t) download and install an evaluation copy of
SQL Server? at’s fine—you can always use the pre-Access 2007 version of
Northwind (Northwind.mdb). Again, if you don’t have this, download instructions
are in the introduction. As another alternative, this book’s Web site has an Excel
version of Northwind if you want to work with that. As of this writing, there is
also a data feed version of Northwind, freely available on the Internet.
This “quick start” chapter uses the SQL Server version of Northwind. If you don’t
have this, alternatives are presented in the next chapter—even so, hopefully, the
examples in this chapter will make perfect sense without having to click along. In
Chapter 2, all of the steps are explained in more detail. In addition, that chapter has
detailed instructions on how to use the Access version, the Excel version, and a data
feed version of Northwind. In the meantime, you may be able to adapt this chapter
to work with the Access version of Northwind. If so, please use the pre-Access 2007
Northwind (Northwind.mdb), so the data and structures match those here. The Excel
and data feed versions of Northwind require a couple of extra steps (specifically, setting
up relationships between tables)—these extra steps are fully documented in Chapter 2.