This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Adding Calculations In PowerPivot
Microsoft introduces a new formula language called DAX (Data Analysis eXpressions). DAX is used to add new calculated fields to the table in PowerPivot and also for adding new measures to the pivot table. DAX shares 81 functions with Excel, so if you are proficient with Excel functions, you should have little problem working in DAX.
The first blank column in the PowerPivot window is called Add Column. Click in any cell in that column. Type an equals sign and enter your formula.
The Year field in the figure below =Year(Sales[Date]). The Month field is =FORMAT(Sales[Date],"MMM").
Figure 955 Add new calculations in the PowerPivot window.
After adding a calculation, the heading for that column will be called CalculatedColumn1. This is a bit frustrating to me. It seems like there should be a way to specify the proper heading before creating the calculation. Instead, you have to right-click the heading and choose Rename Column.
Gotcha: each column can have only one formula. Every row in the column has to have an identical formula. You cannot refer to cells in other rows. Therefore, the concept of cell addresses like A2 is not relevant in PowerPivot. You will always specify the formula using the table nomenclature that was introduced in Excel 2007.
As you are building your formula, you can click a field with the mouse to refer to that field. You cannot use the arrow keys to select a field. When you click a field, PowerPivot will build the syntax of TableName[FieldName]. If you were simply typing the formula, you could leave off the TableName and just type [FieldName]. However, if I am referring to a field in this table, I will be using the mouse to point to that field, so you will always see the full TableName[FieldName] nomenclature in this book.