Watch the video demonstration of how to create calculated columns and fields in Power Pivot.

For any newbie there is always a confusion between calculated columns and calculated fields and when to write what.

Note: Calculated fields are also referred as Measures in BI terminology and is used interchangeably.

Calculated Columns

With calculated column you can add more data to the Power Pivot table using some formula- DAX or inbuilt functions.  For example, Formatted Sales Date, Join of 2 string columns, or many other mathematical formula.  Keep in mind that calculated column is evaluated for each row.

When to use Calculated Column:-

  1. Write “Calculated Columns” when you expect a single value for each row in a table.
  2. While creating a report, anything that is put on rows, columns, slicers, filters OR for a chart values on X/Y axis are columns or calculated columns.

Calculated Fields

Calculated fields and calculated columns are similar as that both are based on formula but it is a single value or multiple value based on grouping of rows. Calculated filed/Measure is the value which need to be analyzed across different parameter to infer a decision.

We use measures to calculate aggregate expressions, especially when we want those to “react” to different pivot layouts.

When to use Calculated Fields:-

  1. While creating a report, value that is put on “Values” are of Pivot table or Pivot Chart
  2. The value of measure keep on changing depending on the layout of the pivot table, columns selected and filters applied.

Tip: Calculated columns values are pre-calculated until the next refresh and does not change with layout of your report, however, measure’s value keep on changing and recalculated every time

Implicit Measures

Animplicit calculated field is created by Excel when you drag a field, such as Sales Amount, to the VALUES area of the PivotTable Fields list. Because implicit calculated fields are generated by Excel, you might not be aware that a new calculated field has been created.

Recommendation:  Implicit calculated fields are limited to standard aggregation like SUM, AVG, Count, MIN, MAX etc. is not recommend to be used with Power Pivot. Always create explicit (manually defined) measures. 

Leave a Comment

Your email address will not be published. Required fields are marked *