Part V: Understanding Power Pivot and Power Query
Creating your first calculated column
Creating a calculated column works very much like building formulas in an Excel table.
Let’s continue the walk-through with some sample data; follow these steps to create a
calculated column:
- Open the Pivot Formulas.xlsx sample file, activate the Power Pivot window
(by clicking the Manage button on the Power Pivot Ribbon tab), and then
select the Invoice Details tab.
- In the table, you will see an empty column on the far right labeled Add
Column. Click the first blank cell in that column.
- In the Formula bar (see Figure 37.1), enter the following formula:
=[UnitPrice]*[Quantity]
- Press Enter to see your formula populate the entire column.
- Power Pivot will automatically rename the column to Calculated Column 1.
Double-click the column label, and rename the column Total Revenue.
FIGURE 37.1
Start your calculated column by entering your desired operation in the Formula bar.
You can rename any column in the Power Pivot window by double-clicking the column name and entering a new name.
Alternatively, you can right-click any column and choose the Rewname Column option.
You can build your calculated columns by clicking instead of typing. For example, instead of manually entering =
[UnitPrice]*[Quantity], you can enter the equal sign (=), click the UnitPrice column, enter the asterisk (*),
and then click the Quantity column. Note that you can also enter your own static data. For example, you can enter a
formula to add a 10 percent tax by entering =[UnitPrice]*1.10.
Each calculated column that you create will automatically be available in any PivotTable
connected to the Power Pivot data model. You don’t have to take any action to get your
calculated columns into the PivotTable. Figure 37.2 illustrates the Total Revenue calculated
