Introduction to Power Pivot - Microsoft Business Intelligence Tools for Excel Analysts - page 54

level; that is, the formulas you create in a calculated column perform their operations based on the
data in each individual row. For example, imagine you have a Revenue column and a Cost column in
your Power Pivot table. You could create a new column that calculates revenue minus cost. This calcu-
lation is simple and valid for each row in the dataset.

Creating a calculated column
Creating a calculated column works very much like building formulas in an Excel table. Follow these
steps to create a calculated column:

1. Open the Power Pivot window and click the Invoice Details tab.

If you inadvertently closed the Power Pivot window, you can open it by clicking the
Manage button on the Power Pivot Ribbon.

Tip

2. Click in the first blank cell in the Add Column column.

3. Enter the following formula on the formula bar:

=[UnitPrice]*[Quantity]

4. Press Enter to see your formula populate the entire column.

Power Pivot automatically renames the column to CalculatedColumn1.

5. Double-click the column label and rename the column Total Revenue.

Your table now has a calculated column similar to that shown in Figure 3-11.

Figure 3-11: Your formula automatically populates all rows in your new calculated column.

6. Create another calculated column called Total Cost.

The formula is =[UnitCost]*[Quantity] .