Delta vs AVG for each row

[Petr__]

Hi guys,

I am trying to understand how DAX works, still fighting with that (I am not strong in contexts but I understand it little bit so please take patient ) I have following table

Char	Value	Avg per group
A	1	2
A	3	2
B	2	5
B	8	5
C	1	50
C	99	50

And my goal is to get new column which will compare current value vs avg for current group (see latest column in right)

Char	Value	Avg per group	Delta from average per group
A	1	2	-1
A	3	2	1
B	2	5	-3
B	8	5	3
C	1	50	-49
C	99	50	40

I tried to use "DELTA = sumx(Avg per group - Values(Value))", context is empty, because it should be evaluated against table above.., I understand that some row context should be used to iterate calculation for each row, but dont t know the formula..

any ideas how to fix it and why it is not working?

thank you in advance

[v-zhouwen-msft]

Hi @Petr__ ,

Regarding your question, it seems that you want to create a calculated column. A calculated column is a row context.You just need to use the following expression.The measure is the filter context.

The following expression means that the operation '[Value] - [Avg per group]' is performed on each row.(Since calculated columns are row context, they all represent values ​​in the same row)

Column = [Value] - [Avg per group]

You also used the 'SUMX' function, whose first argument is a table.

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Petr__]

Hello @v-zhouwen-msft ,

thanks for reply, but my AVG PER GROUP column is already calculated by DAX 🙂 thefore DAX measure for this calculation, sorry if it was confusing

[v-zhouwen-msft · Accepted solution]

Hi @Petr__ ,

Is your requirement to create measures to get your expected results?

The Table data is shown below:

Use the following DAX expression to create measures

AVG PER GROUP = CALCULATE(AVERAGE('Table'[Value]),ALL('Table'[Value]))

DELTA = SUMX(VALUES('Table'[Value]),[Value]) - [AVG PER GROUP]

Final output

If I understand wrongly, please correct me.

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Petr__]

Hello @v-zhouwen-msft ,
thanks for it 🙂
just question, why you used VALUES ?? I tried it as well but didn't help

[v-zhouwen-msft]

Hi @Petr__ ,

I'm just modifying the expression you provided, that's why I'm using the VALUES function.

As I mentioned before, the first argument to the SUMX function needs to be a table.

VALUES is a table function that returns a table. So it can be used as the first argument of the SUMX function.