Forum Discussion

fourmj's avatar
fourmj
Frequent Visitor
4 years ago
Solved

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! 

2 Replies

  • fourmj's avatar
    fourmj
    Frequent 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's avatar
      v-chenwuz-msft
      Icon for Community Support rankCommunity 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.