Advertisements

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.

PowerPivot-18

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.
PowerPivot-19

  • Type Sales Quantity, and then press Enter.

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

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.

Advertisements

Important

Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: