Calculate Mean Absolute Percent Error (MAPE) when all values are in singular column?

[fourmj]

Does anyone know how to calculate MAPE if all the data values are in the same column? 

This is the MAPE Formula for reference: (1/n) * Σ(|actual – forecast| / |actual|) * 100

Here's what my dataset looks like:

Date	Prediction Type	Value
Saturday, August 1, 2020	Actual	100
Sunday, August 2, 2020	Actual	95
Monday, August 3, 2020	Actual	99
Tuesday, August 4, 2020	Actual	1
Wednesday, August 5, 2020	Actual	81
Thursday, August 6, 2020	Actual	98
Friday, August 7, 2020	Actual	105
Saturday, August 1, 2020	Projected	101
Sunday, August 2, 2020	Projected	110
Monday, August 3, 2020	Projected	90
Tuesday, August 4, 2020	Projected	5
Wednesday, August 5, 2020	Projected	79
Thursday, August 6, 2020	Projected	88
Friday, August 7, 2020	Projected	94

I made these measures to try and get the MAPE value: 

7. Backtesting Actuals =
CALCULATE(
    SUM('7_Temp'[Value]),
    '7_Temp'[Prediction Type] = "Actual"
)

7. Backtesting Projections =
CALCULATE(
    SUM('7_Temp'[Value]),
    '7_Temp'[Prediction Type] = "Projected"
)

7. Backtesting MAPE =
VAR MapeElement =
ABS(([7. Backtesting Actuals]-[7. Backtesting Projections])/[7. Backtesting Actuals])

VAR totalYears =
CALCULATE(
     DISTINCTCOUNT('7_Temp'[Date])
)

RETURN

IF(
    HASONEVALUE('7_Temp'[Date]),
    MapeElement,
    AVERAGEX(VALUES('7_Temp'[Date]),
    MapeElement
    )
)

However, my output doesn't quite end up being right. It looks like I'm able to get the Absolute difference between forecast and actuals for each date. However, I'm not able to do a summation and then divide by N of all those absolute differences. This is what I'm getting at the moment:

Does anyone know what I'm doing wrong here? The MAPE of this dataset should actually be 63.8%, not 2%. I'd appreciate any insight on the issue. Thanks! 

[fourmj · Accepted solution]

Edit: So, I found out a way to solve this problem after reading https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/td-p/547907.

First, I modified [7. Backtesting Mape] to solely return MapeElement. 

Then I created a measure:

7. Backtesting MAPE Summarize =
VAR _table =
SUMMARIZE(
     '7_Temp',
     '7_Temp'[Date]
     "_Value", [7. Backtesting MAPE]
)

RETURN

IF(
    HASONEVALUE('7_Temp'[Date]),
   [7. Backtesting MAPE],
    AVERAGEX(_table, [_Value])
    )
)

This measure is returning the correct MAPE value.

[v-chenwuz-msft]

Hi @fourmj ,

Glad you found the answer, please mark it as the correct answer so that more people with the same problem as you can find the answer quickly.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.