Forum Discussion
Calculate Mean Absolute Percent Error (MAPE) when all values are in singular column?
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!
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.
2 Replies
- fourmjFrequent Visitor
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
Community Support
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.