Dax on Calculated Column and Measure

Question

I have done an experiment on iterable and non-iterable filter for row context and filter context in Power BI.

My understanding is measure has filter context while calculated column has row context

This is the Dax for measure:

UnitPrice (Sum) = 
SUM(
    'Sales by Store'[unit_price]
)


UnitPrice (Sumx) = 
SUMX(
    'Sales by Store',
    'Sales by Store'[unit_price]
)


UnitPrice (CalSum) = 
CALCULATE(
    SUM(
        'Sales by Store'[unit_price]
    )
)


UnitPrice (CalSumx) = 
CALCULATE(
    SUMX(
        'Sales by Store',
        'Sales by Store'[unit_price]
    )
)

The output in matrix table is display their respective unit price for each product within the same product category (Whole bean/teas):

This means measure has filter context but why we Sum and Calculate Sum does not display the same amount of total unit price of the product group for each product since both Dax function are not iterable?

For calculated column, I am using another column called Quantity Sold in Power Query.

Qty Sold_1 (Sum) = 
SUM(
    'Sales by Store',
    'Sales by Store'[quantity_sold]
)

Qty Sold_2 (Sumx) = 
SUMX(
     'Sales by Store',
     'Sales by Store'[quantity_sold]
)


Qty Sold_3 (CalSum) = 
CALCULATE(
    SUM(
        'Sales by Store'[quantity_sold]
    )
)


Qty Sold_4 (CalSumx) = 
CALCULATE(
    SUMX(
        'Sales by Store',
        'Sales by Store'[quantity_sold]
    )
)

The output is Sum and Sumx will show the total amount for all the row while Calculate Sum and Calculate Sumx will show their respective unit price. quantity sold is the original column.

Transaction ID	quantity sold	Qty Sold_1 (Sum)	Qty Sold_2 (Sumx)	Qty Sold_3 (CalSum)	Qty Sold_4 (CalSumX)
131	1	6	6	1	1
192	2	6	6	1	1
460	3	6	6	1	1

Why Sumx (able to iterate by row) is not showing the respective quantity sold in row context and Calculate Sum is not showing the total quantity sold for all the row since it is not iterable by row in row context?

Marcus Marcus · Accepted Answer · 2021-09-17T07:57:34

When you write SUM ( Table[Column] ) this is exactly the same as writing SUMX ( Table, Table[Column] ) so when using a SUMX with only one column, these are the same. The flexibility of SUMX is that you can write e.g. SUMX ( Table, Table[Column] * Table[Column2] ) to get something other than just a straight sum, calculated row-by-row in your table.

When you are adding a new calculated column, you need to invoke CALCULATE to transform your table row context into a corresponding filter context. This is called context transition and is very important to understand when writing DAX. Without this context transition, SUM ( Table[Column] ) (and the SUMX equivalent) will give you... the sum of the column. As expected. This has nothing to do with row-by-row iteration.

Dax on Calculated Column and Measure

1 Answers