Problem with AVERAGEIFs

[fjordy]

Hello everyone,

I have a problem that took me a few hours to solve and I couldn't 😞
I have the following data structure:

Country	Year	Month	Product	Receiver	Volume	Razão
USA	2014	1	Soja	China	5354185	Real
USA	2015	1	Soja	China	5260912	Real
USA	2016	1	Soja	China	3261607	Real
USA	2017	1	Soja	China	3483983	Real
USA	2018	1	Soja	China	2690972	Real
USA	2019	1	Soja	China	132999	Real
USA	2020	1	Soja	China	2323474	Real
USA	2021	1	Soja	China	5122018	Real
USA	2022	1	Soja	China	3308130	Real
USA	2023	1	Soja	China	6224062	Real

notice that there are several years and also several months (in this example I put only month 1), in powerbi I already used the conditions "AVERAGEIFS", AVERAGEX with GROUPBY and CALCULATE with FILTER to make the correct average for each month grouping the last five years - for example the average of month 1 of the years 2018 to 2022, but nothing works, it makes an average that definitely the values are incorrect.

Can someone help me please? :'(

[amitchandak]

@fjordy ,

You can try measure like

AVERAGEX(Values(Date[Month Year]) , calculate(Sum(Table[Volume])))

I assumes you are using date table with month year

or

AVERAGEX(Summarize(Table,Table[Year], Table[Month], "_1" , calculate(Sum(Table[Volume]))), [_1])

[fjordy · Accepted solution]

Thank you very much, as it was not a date format I had to use another formula, I managed it as follows:

Average 5 years =
VAR CurrentYear = MAX('Amzr Evolution UF'[Year])-1
VAR LastYear = CurrentYear - 4
VAR TotalVolume = SUMX(
FILTER(
CALCULATETABLE(
'Exp Consolidated',
'Consolidated Exp'[Year] >= LastYear && 'Consolidated Exp'[Year] <= CurrentYear && 'Consolidated Exp'[Volume] <> 0
),
'Consolidated Exp'[Month] = MAX('Consolidated Exp'[Month])
),
'Consolidated Exp' [Volume]
)
VAR QtyYears = 5
RETURN DIVIDE(TotalVolume,QtyYears)