SUMX with calculate average

[sebasjun]

Hello

I need apply this function 

I created a simple datatable

AVGx = AVERAGE(Table1[x]) 

Measure Manual = SUMX(Table1; Table1[x] - 14)

Measure Calc = SUMX(Table1;(Table1[x] - [AVGx]))

Mesaure Manual is my desired result and Measure Calc is my automated measure

The problem is in Measure Calc this  caculate the average per each row and is the same wth x and the result is 0 normal!, i need to calculalte the average total in the context filter (Cliente = C1) --> 14  

Thanks

[OwenAuger · Accepted solution]

Hello @sebasjun

Try this - storing the average in a variable. Previously when the [AVGx] measure was computed in the context of each row, context transition meant that it was just averaging the value on that row.

Measure Calc =
VAR Avg = [AVGx]
RETURN
  SUMX ( Table1; ( Table1[x] - Avg ) )

[v-yuta-msft]

Hi sebasjun,

Change measure [AVGx] to a calculate column [AVGx Column] them modify [Measure Calc] using DAX like this:

Measure Calc = VAR XXX = MAX(Table1[AVGx Column]) RETURN SUMX ( Table1, ( Table1[x] - XXX ) ) 

The result is as below:

 

Regards,

Jimmy Tao

[OwenAuger · Accepted solution]

Hello @sebasjun

Try this - storing the average in a variable. Previously when the [AVGx] measure was computed in the context of each row, context transition meant that it was just averaging the value on that row.

Measure Calc =
VAR Avg = [AVGx]
RETURN
  SUMX ( Table1; ( Table1[x] - Avg ) )

[sebasjun]

Hello

Thanks for you reply, I try and is 0 

I attached the file if you can help me  File

[Stachu · Accepted solution]

I think the average has to be either with ALL or ALLSELECTED

AVGx:=CALCULATE(AVERAGE(Table1[x]),ALLSELECTED(Table1[x]))

[Anonymous]

This measure gets the same results as your manual process

Measure =
SUM ( Table1[x] ) - CALCULATE ( [AVGx], ALLSELECTED ( Table1 ) )