英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论