Where PowerPivot starts to really outshine the simple Pivot Tables is in the feature that allows you to create new columns through the PowerPivot Manager. You can then filter, and report off the results as if it were a normal field in the system.
NOTE: This is part of the Using PowerPivot to Analyze Your Dynamics AX Data blog series where we show how PowerPivot can assist you with advanced data analysis and reporting, making those extracts from Dynamics AX to Excel even more valuable. If you want to see all of the other posts in this series, click here.
HOW TO DO IT
To quickly create calculated columns through PowerPivot, follow these steps:
- Open up the PowerPivot Manager workspace by clicking on the Manage button within the Data Model group of the PowerPivot ribbon bar.
- When the PowerPivot Manager window is displayed, scroll to the very right of the columns, and rename the column that is called Add Column.
- This will add a new column to your table within the data model. You can create a formula for the field just by typing it in or pointing and clicking – just like a normal Excel function.
HOW IT WORKS
The difference is that you only need to do it once and then all of the records will be updated.