Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Bullpro_
Frequent Visitor

Not giving the correct average while calculating the Mean Absolute Percentage Error in DAX

I am analysing a forecast and a station data. During the night, I am getting negative values on the station, and zero values on the forecast data. And there are some days I am missing some data.

 

Keeping that in mind, I am trying to calculate the Mean Absolute Percentage Error (MAPE) in DAX within Power BI to compare my forecast values to my station data. If there is a value equal or less than a 0, or there is not any value, then do nothing. However, I am not getting the correct average for my MAPE. Here's how I am currently calculating it:

 

MAPE =


VAR Actual = AVERAGE(STATION_TABLE[GHI])
VAR Forecast = AVERAGE(FORECAST_TABLE[GHI])
VAR AbsoluteError = ABS(Actual - Forecast)


RETURN

IF(
    OR(Actual <= 0, Forecast <= 0),
    BLANK(),
    DIVIDE(AbsoluteError, Actual)
)

I am then averaging the MAPE for all of my data points using the following measure:


MAPE_Average = AVERAGE([MAPE])

I made a table with the date, hour, station data, the forecast data and the MAPE result to compare the result. The MAPE is correct until it calculate the average.

 

DATEHOURSTATION_DATAFORECAST_DATAMAPE
01/01/202300:00---
01/01/202301:00---
01/01/202302:00---
. . .. . .. . .. . .. . .
05/01/202310:00-45-
05/01/202311:00-78-
05/01/202312:00-100-
. . .. . .. . .. . .. . .
07/01/202310:00-45-
07/01/202311:00-78-
07/01/202312:00-100-
. . .. . .. . .. . .. . .
08/01/202313:00-5.045-
08/01/202314:00-4.678-
08/01/202315:00-5.1100-
. . .. . .. . .. . .. . .
09/01/202312:00

45

498.89%
09/01/202313:0056518.93%
09/01/202314:0010512014.29%
 TOTAL3003093.0%

 

However, when I compare this value to the MAPE total calculated using other tools (such as Excel), I am getting a different average. The average on the table is the value MAPE result of the **TOTAL AVE** values at the end of the table. So, I am not getting the average of the whole column. And the measurement, which I did in apart, is giving another result which is not the same in Excel and neither the table.

 

Can anyone help me identify what I might be doing wrong, or suggest an alternative approach for calculating the MAPE in DAX?

 

Thanks in advance for your help!

1 ACCEPTED SOLUTION
Bullpro_
Frequent Visitor

I found the answer, but it can't be applied in all cases, I just create a new measure that call the measure I posted before:
Measure_2 = 
SUMX(
    VALUES('Fact Table_TIME'[Hour]),
    IF(
       [MAPE] > 0, [MAPE]
    )
)/12

The 12 is the counts of values in the column [MAPE].

View solution in original post

4 REPLIES 4
Bullpro_
Frequent Visitor

I found the answer, but it can't be applied in all cases, I just create a new measure that call the measure I posted before:
Measure_2 = 
SUMX(
    VALUES('Fact Table_TIME'[Hour]),
    IF(
       [MAPE] > 0, [MAPE]
    )
)/12

The 12 is the counts of values in the column [MAPE].

tamerj1
Super User
Super User

@Bullpro_ 

Assumening the date column is from a dim Date table:

MAPE_Average =
AVERAGEX (
    VALUES ( 'Date'[Date] ),
    CALCULATE (
        VAR Actual =
            AVERAGE ( STATION_TABLE[GHI] )
        VAR Forecast =
            AVERAGE ( FORECAST_TABLE[GHI] )
        VAR AbsoluteError =
            ABS ( Actual - Forecast )
        RETURN
            IF (
                OR ( Actual <= 0, Forecast <= 0 ),
                BLANK (),
                DIVIDE ( AbsoluteError, Actual )
            )
    )
)
amitchandak
Super User
Super User

@Bullpro_ , Try like

DIVIDE(Averagex(Values(Date[Date]),calculate(AbsoluteError)), Actual)

Still having the same issue. Does the average function affecting this measure? Another option is to create another new measurement, isn't it? 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors