Create a calculated column in PowerPivot

If you have missed earlier post, please walkthrough them for better understanding from below links:

PowerPivot for Excel

PowerPivot Creating a Data Model in Excel 2013

Adding more tables to the Data Model using Existing Connection – In PowerPivot

Add relationships to Data Model in PowerPivot

How to add Filter for data retrieval in PowerPivot Data model.

In Power Pivot, you can use Data Analysis Expressions (DAX) to add calculations. In this task, I will add simple Excel Formula =ABS (Quantity) in Item Ledger Entry Sheet. You can add formulas either simple calculation from two fields or referencing from other tables and sheet. I will come with more details on this in separate post.

• In the Power Pivot window, switch to Data View.


• Select the Item Ledger Entry table/ sheet.


• Click Design > Add.

Alternatively you can Add by selecting Column, Right Click and choose Insert Column as you do in Excel.

New Column Will be Added.

• In the formula bar above the table, type the following formula. AutoComplete helps you type the fully qualified names of columns and tables, and lists the functions that are available. You can also just click the column and Power Pivot adds the column name to the formula.

In my case it is =abs([Quantity])

• When you have finished building the formula, press Enter to accept the formula.

Values are populated for all the rows in the calculated column. If you scroll down through the table, you will see that rows can have different values for this column, based on the data that is in each row.

Rename the column by right-clicking and selecting Rename Column.

• Type Sales Quantity, and then press Enter.

Below is the Screen after populating the value post formula application to Calculated Column.


I will come up with more details in my upcoming post, Till then keep practicing and stay tuned for more details and updates on this topic.