Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.
Kelly W. wins an autographed copy of PowerPivot for the Data Analyst from CFO and MrExcel for submitting this week’s question: “I am utilizing Pivot Tables to analyze some large amounts of data on purchases. Each row of my pivot table is the ID # of a product so I can clearly see what products are purchased the most frequently and for the most dollars. I also have two columns for ‘Max of Unit Price’ and ‘Min of Unit Price.’
I would like to add a third column ‘Price Range’ that is simply the difference of the two columns already in the Pivot Table. It appears the calculated field option will not work. Every row ends up with a value of $0. I have searched the Internet to find a solution. Unfortunately, while I have found others with the same question I have yet to find a resolution. Any help would be greatly appreciated!”
I love pivot tables, but the calculated field feature leaves a lot to be desired. In the situation described by Kelly, the calculation engine goes to each row in the database, calculates MIN(Price) for that row and MAX(Price) for that row and calculates a difference. Of course, this is useless, since there is only one price on each row in the original database, and that calculation works out to zero for every row. You can show the Sum, Min, Max of a bunch of zeroes and the answer will still be zero.
Fig. 2
PowerPivot to the Rescue
With the release of Office 2010, the SQL Server Analysis Services team at Microsoft released an amazing free tool for Excel called PowerPivot. Anyone with Excel 2010 can download the Excel client version of PowerPivot from www.PowerPivot.com. As I wrote in PowerPivot for the Excel Data Analyst there are the “Big Six” Reasons (not to be confused with the old Big Six accounting firms) why PowerPivot pivot tables run circles around regular pivot tables:
1. Create pivot tables from 100 million rows of data.
2. Mash up data from Sheet1 and Sheet2 without using VLOOKUP.
3. Import data from anywhere; text files, Oracle, Sybase, Teradata, SQL Server, Atom.
4. Create asymmetric pivot tables (think last year actuals vs. next year budget)
5. DAX measures run circles around calculated pivot fields.
6. New time intelligence functions handle fiscal years, parallel periods and more.
After I saw Kelly’s question, I put reason #5 to the test. Within five minutes, I had solved this previously unsolvable problem.
Follow these steps to install PowerPivot:
1. Make sure you have Excel 2010. PowerPivot requires Excel 2010.
2. Go to File, Help. Look on the right side to figure out if you are running 32-bit or 64-bit Excel. You will need this information in step 4.
3. Close Excel.
Fig. 3
4. Browse to www.PowerPivot.com. They offer two free downloads. Download either the 32-bit or 64-bit version to match your version of Excel. (See Figure 3, above)
5. Install PowerPivot.
6. Re-launch Excel. You should now see a PowerPivot tab at the right side of the Excel 2010 ribbon. (Figure 4)
Fig. 4
In the above list, Big Six reason #3 says that you can import data from many different systems. However, in this case, your data is already in Excel. In that case, you can simply copy and paste, or create a linked table. Since Kelly talked about a lot of data, the linked table is probably the way to go.
7. Open the Excel workbook that has the existing data set (Figure 5). Consider deleting any existing pivot tables, since you will be replacing the traditional pivot table with a PowerPivot pivot table.
8. Select one cell in your data and press Ctrl T to declare the dataset as a Table. Click OK. The data will be formatted, but more importantly, Excel understands the data set as a defined table.
Fig. 5
9. On the PowerPivot tab of the Excel ribbon, choose Create Linked Table (Figure 6). You will now see your data in the PowerPivot window (Figure 7).
Fig. 6
Fig. 7
10. From the Home tab in PowerPivot, click on the PivotTable icon. Choose New Worksheet as the location and click OK. You are returned to Excel and a blank pivot table with a slightly different Pivot Table Field List (Figure 8).
Fig. 8
11. Select the Product field and move it to the row labels just as you would do in a regular pivot table.
In PowerPivot, Calculated Fields are Called Measures
In the world of PowerPivot, the calculated field feature is replaced with something called a Measure. Measures are created in a formula language called DAX (Data Analysis eXpressions). DAX offers many of the same functions as in Excel, but offers 80 incredibly powerful new functions.
The DAX language can be used to calculate new columns in the PowerPivot grid, or to calculate new fields in the resulting pivot table. For instance, say that you have a 100-million-row dataset that is summarized in a 9-row pivot table. If you create a calculation in the grid, PowerPivot has to do the calculation a 100 million times. If you create a calculation in the pivot table, PowerPivot only calculates the 9 cells in the final pivot table.
Kelly’s pivot table will require three measures.
12. In Excel, go to the PowerPivot tab of the ribbon. Click New Measure.
13. In the Measure Settings dialog, you have to choose a base table name. (Remember that PowerPivot is able to create pivot tables from multiple worksheets). In this case, there is only one dataset, so leave the Table Name as Table1.
14. Select a name for the field such as MinPrice or MinP.
15. The formula is =MINX(Table1, Table1[Price]), as shown in Figure 9. After typing the formula, you can click Check Formula to make sure you typed the formula correctly. Note that MINX is one of the new functions introduced by PowerPivot.
Fig. 9
16. Click OK. The minimum price for each product will appear in the pivot table. Steps 12-16 are slightly longer than building a Minimum price in a regular pivot table. However, the payoff occurs in step 18.
17. Repeat steps 12-16 to calculate the Max Price. The formula is MAXX(Table1, Table1[Price])
Repeat steps 12-16 to calculate the Range. DAX Measures are allowed to refer to any existing DAX Measures, so the formula is simply: =Table1[MaxP]-Table1[MinP] (Figure 10). By the way, use the AutoComplete function as you type. As soon as you type =T, you can select Table1[MaxP] from the dropdown without having to type the entire syntax. You will now have a pivot table showing the min, max, and range, as shown in Figure 11.
If you add new data to the original dataset, there will be one extra step to refresh the pivot table.
1. On the PowerPivot tab, click Update All
2.On the PivotTable Tools Option tab, click Refresh.
Fig. 10
Fig. 11
Confessions of a PowerPivot Fan
As you read this paragraph, understand that as MrExcel, I might be slightly biased. I believe that the inventors of PowerPivot should win a Nobel Prize. In fact, I believe that the inventors of PowerPivot should win an Oscar, an Emmy, a Grammy, AND a Tony. I believe that the inventors of PowerPivot should win the Luca Pacioli Lifetime Achievement Award for improving the lives of every accountant on earth.
PowerPivot will wrest the power of business intelligence from the IT department and put it in the hands of accounting and finance. People who use Excel have never been offered such a dramatic improvement in Excel. Ever.
If someone had invented a tool that would solve any one of the Big Six problems, it would be amazing. Given that PowerPivot for Excel solves all six of these problems, it becomes the greatest invention for the accounting profession of all time. To recap, PowerPivot pivot tables offer these Big Six benefits:
1. Create pivot tables from more than a million rows of data. 990 Million rows is the current theoretical maximum on a 64-bit machine running 64-bit Excel.
2. Mash up data from multiple worksheets without the calculation expense of using VLOOKUP.
3. Import data from anywhere; text files, Oracle, Sybase, Teradata, SQL Server, Atom, Informix, IBM DB2, Azure Data Mart, Excel, or anything else that supports ODBC or OLEDB.
4. Create asymmetric pivot tables (2010 actuals vs. 2011 budget).
5. DAX measures run circles around calculated pivot fields. They solved Kelly’s problem in this article and can do far more powerful calculations.
6. New time intelligence functions handle fiscal years, parallel periods and more. My heart races just looking at the list of new date and time functions in Figure 12.
Fig. 12
A Word About The Pricing Model
There are two PowerPivot tools.
The tool described in this article and in my book is PowerPivot for Excel. This tool is absolutely free for anyone who has Excel 2010. Microsoft is willing to give away all of this power to 750 million Office users for free.
There is also a server version of PowerPivot that runs with SharePoint. The server version adds some bells and whistles for the IT team and provides the ability to automatically refresh the reports every night. The server version is not free.
As long as you are willing to have an administrative assistant open Excel every morning and click Refresh All, you can access all of power of the Big Six features by using the free PowerPivot for Excel.
PowerPivot saved the day for Kelly’s pivot table, but this use of PowerPivot barely scratches the surface of the power available in PowerPivot.
CFO Contributing Editor Bill Jelen fell in love with PowerPivot the day that he saw it handle 100 million rows in Excel. He is the author of 32 books about Excel including the best-selling PowerPivot for the Excel Data Analyst. Bill will be discussing Pivot Tables in a CFO Webcast on May 5, 2011. You always can win a copy of one of Bill’s books if your question is selected as a topic for an upcoming column. Post your question to the community content block at the right.