Search code examples
powerbidaxpowerquerycalculated-columnsmeasure

Different results when using calculated column and measure in PowerBI

I created a calculated column and measure. They should bring same result. But when creating visuals I get different view.

My calculated column:

enter image description here

if [CostPrice] = null then 0 else [AmountWithDiscount] - [CostPrice]

And visual with this column:

enter image description hereenter image description here

When I try to do the same thing with measure it turns out differently. My measure:

_surcharge = IF(SUM(CheckLines[CostPrice])= BLANK(), 0, SUM(CheckLines[AmountWithDiscount]) - SUM(CheckLines[CostPrice]))

Visual with this measure:

enter image description here enter image description here

It seems to me that logically these two visuals should be the same.

Solution

  • The calculated column is evaluated and calculated for each row, while the measure is evaluated once for all the rows that are in the context.

    This is an important difference that might explain the different numbers that you are getting:

    • The calculated column "SurchargeColumn" is calculated for each row and has the value 0 if the "CostPrice" equals NULL. Then the values of this column are summed up and shown on the first visual.
    • The measure will sum all the values of the "CostPrice" at once. In case the result is blank then it will return 0, otherwise it will return sum of "AmountWithDiscount" minus the sum of "CostPrice". In the measure calculation it doesn't matter if the CostPrice on the individual row is NULL or not; also the rows with CostPrice=NULL are taken into account.