The problem is the presence of duplicate rows in the table being iterated by AVERAGEX
.
AVERAGEX(Products, [Sumx])
will evaluate [Sumx]
once for each row of Products
, each time with a row-context corresponding to the row being visited by the iteration.
Both the first and second copy of the {("Red", 50, TRUE(), 2)}
row will be visited; and set as the current row-context for the iteration step.
In each iteration step, [Sumx]
will transform this row-context into a filter context (due to the implicit context-transition that measures apply), and will get the filter context ("Red", 50, TRUE(), 2)
. This filter context matches two rows of Products
, so the inner SUMX(Products,Products[Amount]*Products[Discount])
will iterate two rows.
The end result is that the AVERAGEX(Products, [Sumx])
computes the following wrong value:
(
2000*2
+(50*2+50*2) //Both `{("Red", 50, TRUE(), 2)}` rows included in filter context of SUMX
+(50*2+50*2) //Both `{("Red", 50, TRUE(), 2)}` rows included in filter context of SUMX again.
)
/3 //3 rows in Products (when Color="Red")
AVERAGEX(Products, Products[Amount]*Products[Discount])
does not apply a context transition to the row context and iterate over the rows in this new filter context, so Products[Amount]*Products[Discount]
evaluates to the correct value for the currently iterated row of Products
.
[Sumx]
measure?Products[Amount]*Products[Discount]
isn't a valid measure definition (or is[Sumx]
actually a calculated column, rather than a measure?). – Mankarse