Calculate average on aggregated row

[Anonymous]

dear PowerBI Gurus

I am addressing a issue that am struggling since a while. I created a database grouping the Actuals and Forecast on a monthly bucket since I want to calculate the accuracy as ABS(Actuals - Forecast)/ABS(Actuals+Forecast).

the problem is that, when I create a table on Item level, it works correctly

SKU	Actuals for FCA	FCST for FCA lag 3M	FCA lag 3 M
1292183	600	360	75.00%
1237537	2400	1084	62.20%
1235035	150	108	83.70%
1217207	1100	378	51.20%
1217206	800	288	52.90%
1217204	940	540	73.00%
1217203	3200	3150	99.20%
1217187	3000	1908	77.80%

however it does not work when I aggregate items according with the item group. I tried with AVERAGEX("demand database",ABS(Actuals - Forecast)/ABS(Actuals+Forecast)) but the formula gives a strange result (last column of below example)

SKU	Actuals for FCA	FCST for FCA lag 3M	FCA lag 3 M	FCA lag 3 M (AVG)
1292183	600	360	75.00%	71%
1237537	2400	1084	62.20%	70%
1235035	150	108	83.70%	71%
1217207	1100	378	51.20%	75%
1217206	800	288	52.90%	71%
1217204	940	540	73.00%	75%
1217203	3200	3150	99.20%	78%
1217187	3000	1908	77.80%	75%

is there a better way to calculate the average accuracy than using AVERAGEX? is it possible somehow to instruct DAX to execute the calculation at item level always and then using the average on aggregated level?

thanks a lot

[amitchandak]

@Anonymous , refer if window function can help

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc