how to find the average of a measure thats used in a Grid

[Anonymous]

Hi Team,

Please find the sample data,

Item Code	Sales Period Name	Promotion Name	Sales Units	Forecast Units	Forecast Accuracy	MAPE
1108344	P12A	P12A 2 FOR �6	5013	1034.11	20.63%	79.37%
1113348	P12A	P12A 2 FOR �6	6231	1717.81	27.57%	72.43%	66.62%
5919436	P12A	P12A 2 FOR �6	2079	492.59	23.69%	76.31%
5919428	P12A	P12A 2 FOR �6	1755	490.4	27.94%	72.06%
5919371	P12A	P12A 2 FOR �6	749	131.46	17.55%	82.45%
1113453	P12A	P12A 2 FOR �6	1425	283.48	19.89%	80.11%
4114167	P12A	P12A 2 FOR �6	34	0	0.00%	100.00%
8446199	P12A	DO MY OFFERS SKINCARE P12	14089	11491.61	81.56%	18.44%
8446199	P12A	P12A SAVE SUMMER REVIVED	14089	11491.61	81.56%	18.44%
			45464	27133.07		40.32%

I have to find the average of the MAPE field.currently MAPE is a measure and its giving the value and shown as a KPI and its current value is 40.32%.But instead of this value i have to get the average value as 66.32.Team any idea how to achieve it.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create measures

Forecast Accuracy = SUM('Table'[Forecast Units])/SUM('Table'[Sales Units])

Mape = 1-[Forecast Accuracy]

Measure = IF(HASONEVALUE('Table'[Item Code]),[Mape],AVERAGEX(ALL('Table'),[Mape]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnthonyTilley]

Measure = 
var fa = AVERAGEX('Table',DIVIDE([Forecast Units],[Sales Units]))
var mape = 1-fa
return mape

[Anonymous]

@AnthonyTilley,Thank you for the reply.

I have tried as you suggested but its not giving the expected result in huge data.

In my scenario i have to use the formula MAPE=ABS(SALES-FORECAST)/SALES 

and need to get the average of MAPE.(AVERAGE(MAPE),similar to selecting a particular column in excel and finding average)

Thank you for you time..