计算 PowerBI 中的 MAE – 无数值错误

huangapple go评论59阅读模式
英文:

Calculate MAE in PowerBI - no values error

问题

我正在尝试在Power BI中计算平均绝对误差(MAE)(使用"新度量"选项)。

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

我的数据具有以下结构(站点数据具有负值,而我的预测具有0值):

日期 小时 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

然而,当我检查我的结果时,我得到:

日期 小时 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

因此,我看到MAE是在STATION[GHI W/sqm]列的正值上计算的,但我不知道为什么没有计算负值。

附注:我必须在MAE中计算平均值,因为这两个数据的步骤不同(站点的步骤为5分钟,而预测的步骤为30分钟)。我使用了一个事实表来关联这两个表格。

英文:

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.

答案1

得分: 2

您在DIVIDE的被除数中有一个非常奇怪的构造。

当您指定这样的公式时:

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

并且'Table'[Column]中包含零值,您将完全从计算中删除这些行,因为引擎将将它们解释为FALSE

因此,对于所有Forecast_1d[GHI W/sqm] = 0的行,DIVIDE将获得一个空白的被除数,并将返回一个空白值。对于您示例数据中STATION[GHI W/sqm]为负数的行,也有Forecast_1d[GHI W/sqm] = 0,这会导致这些行的计算结果为空白。

英文:

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.

huangapple
  • 本文由 发表于 2023年2月8日 20:55:03
  • 转载请务必保留本文链接:https://go.coder-hub.com/75386118.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定