A calculated column is a DAX expression which is evaluated when the PowerPivot workbook is updated. It is very useful, but there are cases where you want to delay calculation at query time. For example:

  • You want to make part of the calculation depending on the selection in the PivotTable (i.e. selecting a scenario for a simulation)
  • You want to avoid the storage of another column in the PowerPivot workbook (it has a cost in terms of space, after all)

First of all, if you have to be worried about storage space, probably you have to look at your data source and check if the calculation can be made on source data, without requiring all the underlying columns to be imported. For example, if you have Quantity and Price and want to calculate Amount (as the product of the formers), you can make this calculation on the source data and import only Quantity and Amount (the Price can be calculated later as an average, which is what people usually want to do).

That said, the point here is that calculating the Amount is pretty simple in a calculated column.

‘Orders'[Amount] = ‘Orders'[Quantity] * ‘Orders'[Price]

However, this formula is not working as calculated measure, because you will get this error message if you try to define a calculated measure with the expression above:

The value for column ‘Quantity’ in table ‘Orders’ cannot be determined in the current context.

To avoid this, you have to make a decision about the aggregation formula you want to use and then using the “X” version. For example, the Amount will be aggregated summing values, so it is safe writing this calculated measure:

‘Orders'[Amount] = SUMX( ‘Orders’, ‘Orders'[Quantity] * ‘Orders'[Price] )

As I said at the beginning, this technique is fundamental whenever you want to delay the product operation at calculation time. In fact, you might have an expression other than a simple product, where part of the calculation depends on the current context of the calculation in the pivot table. More about this in a future post.

SUMX

Returns the sum of an expression evaluated for each row in a table.

SUMX ( <Table>, <Expression> )