Measure with SUMX not returning the same values as Calculated Column

Hi, without looking into the data, its difficult to understand what is happening. Wondering why the fact table in 1 side of the 1-many relationship. However since you have relationship, you can simply use SUM instead of SUMX and it should return correct result.

If this helps to resolve your problem then please mark it as solution, thanks!

Calculated columns are row-by-row calculations stored in your data model, so their results do not change based on slicers or filters. Measures are evaluated dynamically at query time using the current filter context, which allows them to adjust automatically as different filters or slices are applied. In essence, calculated columns add storage overhead but can be used to categorize or group data, while measures offer flexibility and are typically used for aggregations (like sums or averages).

For example, if you create a “Profit Margin” as a calculated column, it will calculate a margin for each transaction row. Summing or averaging that column’s values across multiple rows can produce misleading totals because it simply aggregates row-level margins. In contrast, a measure computing the same margin uses the sum of sales and cost over the entire context, resulting in the correct total margin for that filtered set of data.

Hi JustDavid ,

Thanks for the reply from Thaumaturgist / samratpbi .

The main reason for the difference is that row context does not propagate through relationships, while filter context automatically propagates through relationships based on the cross-filtering direction of the relationship. A filter applied to the "one" side of a relationship affects the table rows on the "many" side of the relationship, but the filter does not propagate in the opposite direction.

For more information about row context and filter context in DAX, please refer to the following document link:

Row Context and Filter Context in DAX - SQLBI

To make a measure the same as a calculated column, you can use the following syntax:

Measure 2 = 
VAR _currentID = SELECTEDVALUE('mirrorFactTable_noAmount'[ID])
RETURN
CALCULATE(SUM('factTable'[Amount]),'factTable'[ID]=_currentID)

This is my test data:

factTable:

mirrorFactTable_noAmountL:

The final page visual effect is as follows:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!