A Simple Tutorial on PowerPivot (3)

 4. Analyze with PowerPivot

The key feature that makes Excel an ideal tool for business analysis is the PivotTable and PivotChart capabilities. Now with Excel 2010, the “slicer” capability (as illustrated below) is also added so that the interactivity is further improved.

PowerPivot provides a set of options to create PivotTable and PivotChart (as illustrated below). Details about each option is out of scope of this tutorial. It is very similar to Excel with more enhancement on slicer and “measures” which can be created by DAX.

One important option, is to create “Flattened PivotTable”. One usage of this option is to print out the data. Another way is to convert the result PivotTable to Excel Formulas so that one can apply Excel functions on the result table.  The option is illustrated below.

It is important to note that the “Field List” for PivotTable or PivotChart under PowerPivot workbooks is different from what it was for other data in Excel. The following picture is an example. The left one is a typical PivotTable Field List while the right one is what you can see in the PowerPivot Field List.

Another interesting feature in PowerPivot is Automatic Relationship Detection. As illustrated below, PowerPivot is being active to detect any potential relationships among tables and fields.

As the core part of data analysis, defining and using measures if always compulsory. PowerPivot provides full support through DAX expressions. Besides the implicit measures which are created while dropping things into the Value area in Pivot chart or Pivot table design. One can also create explicit measures by explicitly specify the DAX expression. To begin creating a measure, right-click on a table name in the PowerPivot field list and select Add New Measure (as illustrated below).

And defining DAX measures requires to use the DAX functions. As illustrated below, the measures are defined in a similar way to Excel formulas.

What to be careful with here is that you should not thing in “rows” when measure values are defined here. And this measure must be defined with an aggregation function (such as sum, count, average, etc.) that will return a single value. In such sense, this is a bit different from the definitions of some calculated columns. This is how it looks like after this measure is defined on the PivotTable.

One more exciting feature to mention, Excel 2010 allows users to create “Named Sets” when using PivotTables (illustrated below). This link provides more detail about this feature: http://blogs.office.com/b/microsoft-excel/archive/2009/10/05/pivottable-named-sets-in-excel-2010.aspx

PowerPivot can also work with Named Sets in Excel.