CODEWITHSUNDEEP
powerbidaxmeasure
Sultry T.
Sultry T.

Reputation: 69

Calculate MAE in PowerBI - no values error

I am tryning to calculate Mean Absolute Error (MAE) in powerbi (using "new measure" option).

MAE_1 =
DIVIDE (
    ABS ( AVERAGE ( 'STATION'[GHI W/sqm] ) - AVERAGE ( 'Forecast_1d'[GHI W/sqm] ) ),
    CALCULATE ( COUNTROWS ( 'Forecast_1d' ), 'Forecast_1d'[GHI W/sqm] )
)

My data has the following struture (the station data has negative value, while my forecast has 0 values):

Date Hour STATION[GHI W/sqm] Forecast_1d[GHI W/sqm]
01/01/2023 0:00 -5.26 0
01/01/2023 1:00 -5.24 0
01/01/2023 2:00 -5.27 0
...
01/01/2023 13:00 800 789
01/01/2023 14:00 799 789
...
01/01/2023 22:00 -5.26 0

However, when I check my results I obtain:

Date Hour STATION[GHI W/sqm] Forecast_1d[GHI W/sqm] MAE_1
01/01/2023 0:00 -5.26 0
01/01/2023 1:00 -5.24 0
01/01/2023 2:00 -5.27 0
...
01/01/2023 13:00 800 789 11
01/01/2023 14:00 799 789 10
...
01/01/2023 22:00 -5.26 0

So I am seeing the MAE it has been calculated on positive values in the STATION[GHI W/sqm] column, but I do not know why I am not calculating the negative values.

P.D: I have to calculate the average inside the MAE, because the steps of both data are deferents (The station has a step of 5 min while the forecast has a step of 30min). I used a Fact table to relate both tables.

Upvotes: 0

Views: 532

Answers (1)

Marcus
Marcus

Reputation: 4015

You have a very strange construct in your DIVIDE divisor.

When you specify a formula like this:

Foo = 
CALCULATE ( 
   [Measure] , 
   'Table'[Column]
)

And your 'Table'[Column] contains zeroes, you will remove these rows from your calculation altogether, as they will be interpreted by the engine as FALSE.

So for all your rows where Forecast_1d[GHI W/sqm] = 0, DIVIDE will get a blank divisor and will return a blank value. For the rows in your example data where STATION[GHI W/sqm] is negative, you also have Forecast_1d[GHI W/sqm] = 0 which causes your calculation to blank out for these rows.

Upvotes: 2

Related Questions