Power Pivot Principles: Calculated Columns
10 April 2018
Welcome back to our Power Pivot blog. Today, we consider how to create a calculated column.
A calculated column is a simple formulaic calculation applied to an entire column that remains in the table you created it in. It can be later used by measures and other calculated columns to create more complex expressions.
Let’s go through an example to walk through how to add a calculated column into the Power Pivot model.
Let’s say we want to add a column into the model which will calculate ‘Gross Profit’. Next to the last column of data in your Power Pivot model will be a blank column with the title ‘Add Column’.
Double click on the heading and type ‘Gross Profit’, note, ensure all columns in your Power Pivot data model have a unique name. In the formula bar, you can type out the formula shown below or by selecting the columns of data you wish to use in the formula:
The new calculated column will appear in the data model as follows:
As this new calculated column contains a formula, it will be computed for each row in the data set. When the underlying data is refreshed the columns are recalculated each time.
This column may now be used in another calculated column, measure, chart, and even PivotTable like any other column in your data model.
Another useful calculated column to have might be a year column. To create this, add a new column, rename it ‘Year’ and use the YEAR function: