In my previous blog, I discussed the PowerPivot workbook. I looked at how to create it by importing data, and I briefly covered the new Data Analysis Expressions (DAX) that are used to create new measures. In this blog, I would like to look at the presentation of PowerPivot data in worksheets.
Worksheets created from the new workbook house PivotTables and PivotCharts. You can have PowerPivot create the workbook with one of several possible layouts that include one or more PivotTables and PivotCharts.
After the layout is determined, you simply select the PivotTable or PivotChart to work on and drag the fields you want into lists in the task pane. Adding a field to the axis, label, filter, and value lists, automatically changes the related table or chart to reflect aggregations of the data including subtotals and grand totals. PowerPivot creates each aggregation based on the relationships between the PowerPivot tables in the workbook. All of the aggregates are SUMs by default. However, it is a simple matter to change the default operation to calculate and average, minimum, maximum, or count.
Fields are also used to create slicers. Slicers add components to the worksheet that contain buttons for selecting data to display in the PivotTable or PivotChart. Each button represents a distinct value from the field being used. If several slicers are added, then you can use any combination of selections to filter the data. The combinations of filters are limited only by your imagination and can range from predetermined business criteria to random combinations. I used the Contoso retail database to create my workbook. I was able to design a PowerPivot PivotTable that showed me all of the sales filtered by product category and subcategory. Then I added demographic slicers that enabled me to change the sales figures based on customer gender, income, marital status, sales source (reseller or individual), and territory. Being able to do all of this within about ten minutes was very cool! I am used to spending hours or days with various report writers trying to perform simpler data analysis reports.
Most of what PowerPivot can do to analyze and present data is automatic and requires very little operator assistance. Naturally, you can spend a little extra setup time and manually create measures using DAX. But, you can also create reports from PowerPivot workbook tables using the new Excel cube functions. This is considered advanced PowerPivot. For an excellent example of using PowerPivot and cube formulas, you can go to http://powerpivotpro.com/2010/01/18/pulling-back-the-curtain-intro-to-cube-formulas and walk through the tutorial.
For now, PowerPivot for Excel is still part of Microsoft Office Excel 2010 Beta. That means if you want to try it out you should be prepared to deal some idiosyncrasies until the final release. Nevertheless, I can guarantee that if you wait for the official release of Excel 2010 and PowerPivot for Excel, you will not be disappointed in the least.
Oh! Did I mention that PowerPivot for Excel is a FREE add-in!






