Calculate monthly data accuracy

[moia79]

Dear gurus

I am facing the following challenge to calculate data accuracy for aggregate of products and time buckets

My data source has the following structure

Item	Description	Item group	Dimension	year	month	quantity
abc	abc_desc	group 1	Actuals	2022	10	512
abc	abc_desc	group 1	Forecast	2022	10	500
abc	abc_desc	group 1	Actuals	2022	9	485
abc	abc_desc	group 1	Forecast	2022	9	400
cde	cde_desc	group 1	Actuals	2022	9	25
cde	cde_desc	group 1	Forecast	2022	9	20
efg	efg_desc	group 2	Actuals	2022	8	4000
efg	efg_desc	group 2	Forecast	2022	8	3750
ghi	ghi_desc	group 2	Actuals	2022	8	800
ghi	ghi_desc	group 2	Forecast	2022	8	925

I would need to calculate the accuracy per month and item group, i.e the result should me

	2022_08
Item group	Actuals	Forecast	FCA
group 1	Σ all actuals of group 1 in month 08	Σ all Forecast of group 1 in month 08	Average of (Fcast - actual)/(Forecast+actual) calculated at item level for all items of group 1
group 2	Σ all actuals of group 2 in month 08	Σ all Forecast of group 2 in month 08	Average of (Fcast - actual)/(Forecast+actual) calculated at item level for all items of group 2

the calculation should be for all months that are selected via a slicer. 

Am struggling with the sum and how to reflect in different months, anyone has a suggestion on how to build the file?

thanks a lot

[djurecicK2 · Accepted solution]

Hi @moia79 ,

 This might help get you started- create a measure for actuals and a measure for forecast.

Actuals= CALCULATE(SUM(Tablename[quantity]), FILTER(Tablename,Tablename[Dimension]="Actuals"))

Forecast= CALCULATE(SUM(Tablename[quantity]), FILTER(Tablename,Tablename[Dimension]="Forecast"))

Then add a matrix visual and put item group in the Rows and Actuals and Forecast in the Values. You can add a filter to the matrix to select a particular month. You could also add Month to the matrix Columns.