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

[Anonymous]

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.

DATE	HOUR	STATION_DATA	FORECAST_DATA	MAPE
01/01/2023	00:00	-	-	-
01/01/2023	01:00	-	-	-
01/01/2023	02:00	-	-	-
. . .	. . .	. . .	. . .	. . .
05/01/2023	10:00	-	45	-
05/01/2023	11:00	-	78	-
05/01/2023	12:00	-	100	-
. . .	. . .	. . .	. . .	. . .
07/01/2023	10:00	-	45	-
07/01/2023	11:00	-	78	-
07/01/2023	12:00	-	100	-
. . .	. . .	. . .	. . .	. . .
08/01/2023	13:00	-5.0	45	-
08/01/2023	14:00	-4.6	78	-
08/01/2023	15:00	-5.1	100	-
. . .	. . .	. . .	. . .	. . .
09/01/2023	12:00	45	49	8.89%
09/01/2023	13:00	56	51	8.93%
09/01/2023	14:00	105	120	14.29%
	TOTAL	300	309	3.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!

[Anonymous · Accepted solution]

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]

@Anonymous 

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]

@Anonymous , Try like

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

[Anonymous]

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