DAX - Reference measure in calculated column?

Question

I have data like this

EmployeeID Value
1             7
2             6
3             5
4             3

I would like to create a DAX calculated column (or do I need a measure?) that gives me for each row, Value - AVG() of selected rows. So if the AVG() of the above 4 rows is 5.25, I would get results like this

EmployeeID Value  Diff
1             7    1.75
2             6    0.75
3             5    -0.25
4             3    -1.75

Still learning DAX, I cannot figure out how to implement this? Thanks

Answer 1

I figured this out with the help of some folks on MSDN forums. This will only work as a measure because measures are selection aware while calculated columns are not. The Average stored in a variable is critical. ALLSELECTED() gives you the current selection in a pivot table. AVERAGEX does the row value - avg of selection.

Diff:= Var ptAVG = CALCULATE(AVERAGE[Value],ALLSELECTED())

RETURN AVERAGEX(Employee, Value - ptAVG)

Answer 2

You can certainly do this with a calculated column. It's simply

Diff = TableName[Value] - AVERAGE(TableName[Value])

Note that this averages over all employees. If you want to average over only specific groups, then more work needs to be done.