Forum Discussion
Delta vs AVG for each row
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
Hi Anonymous ,
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.
5 Replies
Hi Anonymous ,
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.Hi Anonymous ,
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.