I'm new in Power BI and I'm trying to understand some functions.
I tried 2 ways:
1 - Explicitying having the calculation in the AVERAGEX function Avg X = AVERAGEX(Products, Products[Amount]*Products[Discount])
2 - I tried to simplify the above by calculating the Products[Amount]*Products[Discount] in a measure first and then use it in the AVERAGEX Avg = AVERAGEX(Products, [Sumx])
However, I'm getting 2 different values for the last row in my table
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.