How to average by category in measure with summarized table

[Anonymous]

Hi,

I have table 1 listed below in my dataset. What I want to do is to sum all the net requirements by Destination and File Month, and then take the average of net requirements by file month for each destination.  How would I do that in a measure? The 'material' column is irrelevant to the calculation. The intermediate calculation basically sums net requirements listed in table 1 by destination and file month. The desired result averages the intermediate calculation table by destination. Thank you!

Here's the sample data:

Destination	File Month	Material	Net Requirements
Germany	Jan-21	A	45
Germany	Jan-21	B	98
Germany	Jan-21	C	65
Germany	Feb-21	D	78
Germany	Feb-21	B	84
Germany	Feb-21	A	53
USA	Jan-21	A	96
USA	Jan-21	B	52
USA	Jan-21	C	35
USA	Jan-21	D	5
USA	Jan-21	E	48
USA	Jan-21	F	32
USA	Jan-21	G	56
USA	Feb-21	A	35
USA	Feb-21	C	23
USA	Feb-21	F	98
UK	Jan-21	D	45
UK	Jan-21	E	25
UK	Jan-21	F	95
UK	Feb-21	G	78
UK	Feb-21	A	15
UK	Feb-21	C	25
UK	Feb-21	F	35
UK	Feb-21	B	95
UK	Feb-21	D	87

[ryan_mayu · Accepted solution]

@Anonymous 

you can try this

Measure = 
VAR tbl=SUMMARIZE('Table','Table'[Destination],'Table'[File Month],"net",sum('Table'[Net Requirements]))
return AVERAGEX(tbl,[net])

[Anonymous]

Wow, that works perfectly, thank you!

[ryan_mayu]

you are welcome