Forum Discussion
Group by + avg
Hello guys,
I need to help with following group by and average dax formula
my data
| y_m | value |
| 2022_1 | 1 |
| 2022_1 | 2 |
| 2022_1 | 3 |
| 2022_2 | 4 |
| 2022_2 | 5 |
| 2022_2 | 6 |
| 2022_3 | 7 |
| 2022_3 | 8 |
| 2022_3 | 9 |
and I would like to get
| y_m | value |
| 2022_1 | 2 |
| 2022_2 | 5 |
| 2022_3 | 8 |
I tried to this dax formula
measure = GROUPBY(Date_Dim,Date_Dim[y_m],"result",AVERAGEX(CURRENTGROUP(),fact[abs(value)]))
but receive an error:
The CALCULATE formula cannot be used in expression argument for the GROUPBY() function
any idea ? how to do it ?
any idea ? how to do it ?
Anonymous Measures cannot return tables. Your original formula works for me to create a table:
Table 2 = GROUPBY('Table','Table'[y_m],"result",AVERAGEX(CURRENTGROUP(),'Table'[value]))Also, it works in a measure to create a table var:
Measure = VAR __Table = GROUPBY('Table','Table'[y_m],"result",AVERAGEX(CURRENTGROUP(),'Table'[value])) VAR __y_m = MAX('Table'[y_m]) RETURN MAXX(FILTER(__Table,[y_m] = __y_m),[result])
3 Replies
Anonymous Just use measure = AVERAGE('Table'[value])
Hello Greg_Deckler
but I need that output will be table not one value
Anonymous Measures cannot return tables. Your original formula works for me to create a table:
Table 2 = GROUPBY('Table','Table'[y_m],"result",AVERAGEX(CURRENTGROUP(),'Table'[value]))Also, it works in a measure to create a table var:
Measure = VAR __Table = GROUPBY('Table','Table'[y_m],"result",AVERAGEX(CURRENTGROUP(),'Table'[value])) VAR __y_m = MAX('Table'[y_m]) RETURN MAXX(FILTER(__Table,[y_m] = __y_m),[result])