Group by + avg

[Petr__]

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 ?

[Greg_Deckler]

@Petr__ Just use measure = AVERAGE('Table'[value])

[Petr__]

Hello @Greg_Deckler 

but I need that output will be table not one value

[Greg_Deckler · Accepted solution]

@Petr__ 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])