A few pages ago, I talked about all the bad side-effects of having PowerPivot use OLAP pivot tables. Here is one advantage that you can take advantage of because it is an OLAP table. In this case, I might take a regular flat Excel data set through PowerPivot to take advantage of the cube formulas.
Say that you build a pivot table in PowerPivot. You can go the PivotTable Tools Option ribbon tab, choose OLAP Tools and Convert to Formulas.
- Change the pivot table to formulas.
After invoking this command, the pivot table changes to formulas using the cube functions. Even though the pivot table no longer exists, the formulas continue to respond to the slicers!
- You can move these cells around, insert blank rows, and so on.