DAX Query to get average of a column within the same table

2
votes

We have a table named MetricsTable which has columns A1 and Group simply.

We want to add a calculated column AvgA1 to this table which calculates the average of column A1 filtered by the value of Group . What should be our DAX query? The point is that we want to claculate the average from the values within the same table.

| id | A1| Group | AvgA1

| -- | --- | --- ------| ----

| 1 | 20 | Group1| 20

| 2 | 10 | Group2| 30

| 3 | 50 | Group2| 30

| 4 | 30 | Group2| 30

| 5 | 35 | Group3| 35

Regards

1
powerpivotdax

1 Answers

3
votes

Likely you should use a measure and put that measure into a pivot table's 'Values' section:

AverageA1:=
AVERAGE( Metrics[A1] )

Then it will be updated based on filter and slicer selections in the pivot table, and subtotaled appropriately across various dimension categories.

If it strictly needs to be a column in the table for reasons not enumerated in your question, then the following will work:

AverageA1 =
CALCULATE(
    AVERAGE( Metrics[A1] )
    ,ALLEXCEPT( Metrics, Metrics[Group] )
)

CALCULATE() takes an expression and a list of 0-N arguments to modify the filter context in which that expression is evaluated.

ALLEXCEPT() takes a table, and a list of 1-N fields from which to preserve context. The current (row) context in the evaluation of this column definition is the value of every field on that row. We remove the context from ALL fields EXCEPT those named in arguments 2-N of ALLEXCEPT(). Thus we preserve the row context of [Group], and calculate an average across the table where that [Group] is the same as in the current context.