1: Summarize total sales in each state by year.

W1 Chapter 84. PowerPivot

TEXT: Microsoft® Excel® 2010: Data Analysis and Business Modeling

Wayne L. Winston

Questions answered in this chapter:

How do I read data into PowerPivot?

How do I use PowerPivot to create a PivotTable?

How can I use slicers with PowerPivot?

What are DAX functions?

Microsoft PowerPivot for Excel 2010 is an add-in that enables you to easily create PivotTables for large data sets—up to 100,000,000 rows of data! Most amazingly, the data can come from a variety of sources. For example, some of your data might come from a Microsoft Access database, some from a text file, some from several Excel files, and some from live data imported from a website. In this chapter, I provide an introduction to PowerPivot. For a more complete explanation of how PowerPivot works, I heartily recommend Microsoft PowerPivot for Excel 2010: Give Your Data Meaning (Microsoft Press 2010), by Marco Russo and Alberto Ferrari.

You can download PowerPivot from www.powerpivot.com. A separate version of PowerPivot is available for 32-bit and 64-bit versions of Excel 2010. These versions are not interchangeable, so be sure you download the correct version.

Answers to This Chapter’s Questions: How do I read data into PowerPivot?

After you install PowerPivot, you see a PowerPivot tab on the ribbon. Clicking the PowerPivot tab displays the options shown in Figure 84-1.

Figure 84-1. PowerPivot options.

Clicking the PowerPivot Window button in the Launch group opens the PowerPivot window and displays the Home tab, part of which is shown in Figure 84-2. (The remainder of the PowerPivot window’s Home tab does not concern us.)

Figure 84-2. The PowerPivot window Home tab.

If you installed PowerPivot after October 2010, you will also see an option for getting external data from the Windows Azure Datamarket. This option enables you to conduct PowerPivot analyses on a variety of interesting data sets described at datamarket.azure.com. For example, you can download game-by-game statistics from all NFL games and break down how each team performed rushing and passing against teams in its own division.

On the Home tab, you can import data from multiple sources. If you select From Database, PowerPivot will accept input from an Access or Microsoft SQL Server database, for example. If you select From Report, PowerPivot will accept input from a Microsoft SQL Server Reporting Services report. Selecting From Data Feeds allows you to read data from a website that has an Atom feed.

Note

For more information on Atom feeds, see social.technet.microsoft.com/wiki/contents/articles/ssrs-2008-r2-atom-data-feeds-for-powerpivot.aspx.

From Text allows you to import data from a text file, and From Other Sources allows you to read data from the previously described sources as well as from Excel files and many other types of databases, such as Oracle and Teradata.

After copying data from Excel, you can select Paste to insert the data into PowerPivot.

To illustrate how to download data from multiple sources into PowerPivot, I’ll use the text file Storesales.txt, in which I’ve listed sales transactions from 20 stores. A subset of the data is shown in Figure 84-3. You can see that for each transaction, I am given the store number, the product sold, sale date, units sold, and revenue. I want to summarize this data by state, but the state for each store is listed in a different file, States.xlsx. The location of each store is shown in Figure 84-4.

Figure 84-3. Sales data to be imported into PowerPivot.

Figure 84-4. Location of each store.

I want to create a PivotTable that lets me slice and dice my data so that I can view how I did selling each product in each state. To begin, I select the PowerPivot tab and then click PowerPivot Window in the Launch group (as shown at the left in Figure 84-1). This brings up the Home tab in the PowerPivot window. Because I want to import a text file, I select From Text on the Home tab. As shown in Figure 84-5, I can then browse to the file Storesales.txt. Because the first row of data contains column headers, I select the Use First Row As Column Headers option. In the Column Separator list, I select Tab because the data fields in the text file are not separated by spaces or a character, such as a comma or a semicolon. Clicking Finish imports the text file’s data into PowerPivot.

Figure 84-5. Setting up a text file import for PowerPivot.

Figure 84-6 shows the result after the text data is imported into PowerPivot. A subset of the data is shown. At the bottom you can see a tab indicating that the source of the data is Storesales.txt.

Figure 84-6. Subset of the data imported from Storesales.txt.

Next, I want to import the file States.xlsx so that PowerPivot will know the state in which each store is located. To import States.xlsx, I return to Excel by clicking the Excel icon in the upper-left corner of the PowerPivot ribbon. Then I open the file States.xlsx and copy the data I need. Now, I select Paste on the Home tab in PowerPivot (see Figure 84-2). The Paste Preview dialog box shown in Figure 84-7 appears.

Figure 84-7. Paste Preview dialog box.

After I select Use First Row As Column Headers and click OK, the data from States.xlsx is imported into PowerPivot, as shown in Figure 84-8. Note that at the bottom of the window, a tab appears for each data source used to import data into PowerPivot. I left the name for States.xlsx as Table, but you can easily change the name.

Figure 84-8. Data from two sources is now imported into PowerPivot.

Recall that I want to analyze sales in different states. The problem is that at present PowerPivot does not know that the listing of store locations from States.xlsx corresponds to the stores listed in the text file. To remedy this problem, I need to create a relationship between the two data sources. To create this relationship, I click Design on the ribbon in the PowerPivot window (see Figure 84-2) to display the Design tab shown in Figure 84-9.

PowerPivot Design tab.

Figure 84-9. PowerPivot Design tab.

On the Design tab, I click Create Relationship to open the dialog box shown in Figure 84-10. Filling in the dialog box as shown ensures that PowerPivot recognizes that the Store columns in each data source refer to the same set of stores. For example, PowerPivot now recognizes that Stores 19 and 20 in the file Storesales.txt are in Iowa, as shown in the file States.xlsx.

Figure 84-10. Creating a relationship between two data sources.

How do I use PowerPivot to create a PivotTable?

Now, I’m ready to use PowerPivot to summarize our company’s sales data via a PivotTable. I display the Home tab in the PowerPivot window and then select the PivotTable option. The Create PivotTable dialog box appears and prompts me to choose a new worksheet or a location in the current worksheet. I choose a new worksheet. Then the PowerPivot Field List is displayed, shown in Figure 84-11.

Figure 84-11. PowerPivot Field List.

When I click Table, the field list shows all the column headings (States and Stores) for the Table data. The goal is to get a breakdown by state and product of total revenue and units sold. To summarize revenue and units sold, I drag the Revenue and Units fields to the Values area. Next, I drag State to the Row Labels area and Product to the Column Labels area to arrange the PivotTable fields as shown in Figure 84-12. Note that the fields used in the PivotTable are selected.

Figure 84-12. Assignment of fields to create a PivotTable report.

The portion of the PivotTable involving CDs, DVDs, and food is shown in Figure 84-13. As you can see, 3,881 DVDs were sold in Illinois (ILL) for a total revenue of $2,295.76.

Figure 84-13. PivotTable breaking down product sales by state.

How can I use slicers with PowerPivot?

In Chapter 43, I showed how to use slicers to reveal details and different perspectives in your PivotTable analyses. Slicers look even nicer in PowerPivot PivotTables. Here, I’ll create slicers that summarize the data for any subset of products and stores. To do this, I place Product in the Slicers Horizontal field area and Store in the Slicers Vertical area in the PowerPivot Field List. The resulting slicers are shown in Figure 84-14. (See the file Pivotwithslicers.xlsx.) As described in Chapter 43, you can hold down the Ctrl key as you click to select any subset of products and/or stores. Holding down the Ctrl key also enables you to resize the slicers. The PivotTable shown in Figure 84-14 gives the total revenue and units sold of DVDs and food in Stores 7 through 11. Since Stores 7 through 11 are all in Illinois or Michigan, these are the only states shown in the resulting PivotTable. If I had created the Product slicer with the ordinary Excel PivotTable functionality, all six products would be listed in a single column. I think you would agree that the PowerPivot Product slicer looks much nicer.

Figure 84-14. Product and store slicers.

What are DAX functions?

Recall from Chapter 43 that you can generate new formulas in a PivotTable by using calculated items or calculated fields. After your data is imported into PowerPivot, you can use the DAX (short for Data Analysis Expressions) formula language to create new calculated columns that make your PivotTables much more meaningful.

Note: A full discussion of DAX is beyond the scope of this chapter. Microsoft PowerPivot for Excel 2010: Give Your Data Meaning includes an excellent and complete discussion of the DAX language.

To illustrate a DAX formula, I’ll show how to place the year, month, and day of the month for each sales transaction in a separate column. To begin, I click the Storessales tab in the PowerPivot window and select the first blank column. Clicking the fx button below the PowerPivot ribbon brings up a list of DAX functions. Many of these (such as YEAR, MONTH, and DAY) are virtually identical to ordinary Excel functions. Selecting Filter brings up the list of DAX functions shown in Figure 84-15. These are not your mother’s Excel functions! For example, the DISTINCT function can return a list of entries in a column that meet a specified criterion.

Figure 84-15. Listing of DAX functions.

To place the year, month, and day of the month for each transaction in a separate column, move to the first cell of the first blank column and type =YEAR(st. Then you are prompted with the columns from the PowerPivot data sources that you can apply the YEAR function to. Select the date column, and enter the formula =YEAR(storesales[Date]). The column is now populated with the year of each sales transaction. By right-clicking the column heading, I can rename the column Year. In the next column I compute the month of the year by entering the formula =MONTH(storesales[Date]). In the next column I compute the day of the month with the formula =DAY(storesales[Date]). After I rename these columns Month and Day Of Month, the data is arranged as shown in Figure 84-16.

Figure 84-16. Year, Month, and Day Of Month columns created with DAX formulas.

Now I can create a variety of informative PivotTables. For example, I can summarize sales in each state by year. (See Problem 1.)

Problems

Q 1: Summarize total sales in each state by year.

Q 2: Summarize total revenue by store and create a slicer for stores.