CODEWITHSUNDEEP
daxpowerbi-desktop
Camus
Camus

Reputation: 846

Averaging From Measure

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

enter image description here

enter image description here

Upvotes: 2

Views: 131

Answers (1)

Mankarse
Mankarse

Reputation: 40603

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.

Upvotes: 1

Related Questions