blocks|key|18688|text|解决这一问题的一种方法是使用一个名为MAPEX的自定义列，而不是用于计算的度量。如果有特殊的原因，你需要使用DAX，请随时让我知道，我可能会想出一些事情。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|18689|列=ABS((实际操作-预测)/Actuals)|18690|编辑:以防万一，您创建新列的方式是在视图选项卡中使用此按钮。|18691|​|18692|📷|atomic|offset|length|18693|18694|或者，您可以在看来适合我的查询编辑器中创建自定义列。|18695|18696|18697|18698|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/c367176a12dbfc39ed2aaf77c4e31f04.png|imageAlt|1|https://developer.qcloudimg.com/http-save/yehe-900000/7923cc3ae5491b77d2872cf09e418f56.png^0|0|0|0|0|0|1|0|0|0|0|0|0|1|1|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|14|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|15|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|16|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|17|8|@]|9|@$K|18|L|19|1|1A]]|A|$]]|$1|M|3|G|5|6|7|1B|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1C|8|@]|9|@]|A|$]]|$1|P|3|G|5|6|7|1D|8|@]|9|@]|A|$]]|$1|Q|3|I|5|J|7|1E|8|@]|9|@$K|1F|L|1G|1|1H]]|A|$]]|$1|R|3|G|5|6|7|1I|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z|10|-4]]|11|$5|V|W|X|A|$Y|12|10|-4]]]]

One way to solve this would be to use a custom column titled MAPEX instead of a measure that does your calculation. If there is a particular reason you need to use DAX please feel free to let me know and I may be able to figure something out.

Column = ABS(([Actuals]-[Forecast])/[Actuals])

EDIT: Just in case, the way you create a new column is with this button in the view tab.

<a href="https://i.stack.imgur.com/WxEgN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WxEgN.png" alt="Add New Column"></a>

Alternatively, you can create the custom column from within the query editor which appears to be working for me.

<a href="https://i.stack.imgur.com/f1wcO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/f1wcO.png" alt="enter image description here"></a>

blocks|key|509181|text|就这样吧|type|unstyled|depth|inlineStyleRanges|entityRanges|data|509182|变量_mytable+=+SELECTCOLUMNS(FactTable，"MAPE"，ABS(实际操作-预测)/Actuals)返回Sumx(_mytable，MAPE)|509183|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Go with this

VAR _mytable = SELECTCOLUMNS(FactTable, "MAPE", ABS(Actuals - Forecast)/Actuals))
Return
Sumx(_mytable, [MAPE])

blocks|key|18786|text|Seymour的回答看起来不错，但我想补充一点，在这个场景中，粒度很重要。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|18787|假设您有一个像这这样的星型模式，您可以用下面的公式定义度量总量预测、总实际误差、绝对误差和绝对百分比误差。|18788|Total+Forecast++++++++++++=+SUM+(+Forecast[Forecast]+)
Total+Actual++++++++++++++=+SUM+(+Actual[Actual]+)
Absolute+Error++++++++++++=+ABS+(+[Total+Forecast]+-+[Total+Actual]+)
Absolute+Percentage+Error+=+DIVIDE+(+[Absolute+Error],+[Total+Actual]+)|code-block|syntax|javascript|18789|这是你到目前为止要做的事情。|18790|​|18791|📷|atomic|18792|18793|在这里，您要问的是如何计算绝对百分比误差之和。|18794|根据定义，绝对百分比误差表示绝对误差除以实际总的值，而不考虑钻取级别。因此，在总数中，它显示为0.52，即绝对误差(1,457.27)除以实际总数(2,786.27)。如果您希望它在总计级别上进行不同的计算，则需要显式地实现此逻辑。|18795|您的要求将更明确地说明如下：|18796|计算在每个ItemName、年份和月份粒度中的绝对百分比误差值。|ordered-list-item|18797|将它们相加到中。|18798|实现此逻辑所需的函数是SUMX。此外，您可以显式地使用SUMMARIZE来确保计算特定粒度中的绝对百分比错误。|CODE|18799|MAPEX+=+SUMX+(
++++SUMMARIZE+(
++++++++Forecast,
++++++++'Product'[ItemName],
++++++++'Calendar'[Year],
++++++++'Calendar'[Month]
++++),
++++[Absolute+Percentage+Error]
)|18800|18801|18802|18803|到目前为止，我一直在强调粒度。这是因为如果您没有意识到粒度，结果在某些情况下可能看起来很奇怪。|18804|在上面的图像中，MAPEX看起来与绝对百分比误差相同，但总误差除外。然而，如果你按季度而不是按月进行向下钻，你会注意到这是完全不一样的。|18805|18806|18807|18808|绝对百分比误差在季度级别显示绝对误差和总实际值的商数，而MAPEX仍在汇总绝对百分比误差的月值，尽管表中没有显示月份。|18809|所以，我的最后一句话是，每当你发明像MAPEX这样的新方法时，你总是需要问自己，它是否对每一个可能的颗粒都有意义。|18810|entityMap|0|LINK|mutability|MUTABLE|url|https://i.stack.imgur.com/ecDWh.png|1|IMAGE|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/9c2aea3865683c00b85482a47790c709.png|imageAlt|2|https://developer.qcloudimg.com/http-save/yehe-900000/6f50d2aaf16aeb6c55033a6885ffeb83.png|3|https://developer.qcloudimg.com/http-save/yehe-900000/e61f650e322dbd6a9951a87e2eb65da0.png^0|V|2|0|7|1|0|0|0|0|0|0|1|1|0|0|0|N|B|0|0|2|H|0|0|6|0|B|4|R|9|0|0|0|0|1|2|0|0|0|0|0|0|1|3|0|0|8|2|19|1|0|0^^$0|@$1|2|3|4|5|6|7|26|8|@$9|27|A|28|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|29|8|@]|D|@$9|2A|A|2B|1|2C]]|E|$]]|$1|H|3|I|5|J|7|2D|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|2E|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|2F|8|@]|D|@]|E|$]]|$1|Q|3|R|5|S|7|2G|8|@]|D|@$9|2H|A|2I|1|2J]]|E|$]]|$1|T|3|P|5|6|7|2K|8|@]|D|@]|E|$]]|$1|U|3|V|5|6|7|2L|8|@]|D|@]|E|$]]|$1|W|3|X|5|6|7|2M|8|@$9|2N|A|2O|B|C]]|D|@]|E|$]]|$1|Y|3|Z|5|6|7|2P|8|@]|D|@]|E|$]]|$1|10|3|11|5|12|7|2Q|8|@$9|2R|A|2S|B|C]]|D|@]|E|$]]|$1|13|3|14|5|12|7|2T|8|@$9|2U|A|2V|B|C]]|D|@]|E|$]]|$1|15|3|16|5|6|7|2W|8|@$9|2X|A|2Y|B|17]|$9|2Z|A|30|B|17]]|D|@]|E|$]]|$1|18|3|19|5|J|7|31|8|@]|D|@]|E|$K|L]]|$1|1A|3|P|5|6|7|32|8|@]|D|@]|E|$]]|$1|1B|3|R|5|S|7|33|8|@]|D|@$9|34|A|35|1|36]]|E|$]]|$1|1C|3|P|5|6|7|37|8|@]|D|@]|E|$]]|$1|1D|3|1E|5|6|7|38|8|@]|D|@]|E|$]]|$1|1F|3|1G|5|6|7|39|8|@]|D|@]|E|$]]|$1|1H|3|P|5|6|7|3A|8|@]|D|@]|E|$]]|$1|1I|3|R|5|S|7|3B|8|@]|D|@$9|3C|A|3D|1|3E]]|E|$]]|$1|1J|3|P|5|6|7|3F|8|@]|D|@]|E|$]]|$1|1K|3|1L|5|6|7|3G|8|@$9|3H|A|3I|B|C]|$9|3J|A|3K|B|C]]|D|@]|E|$]]|$1|1M|3|1N|5|6|7|3L|8|@]|D|@]|E|$]]|$1|1O|3|-4|5|6|7|3M|8|@]|D|@]|E|$]]]|1P|$1Q|$5|1R|1S|1T|E|$1U|1V]]|1W|$5|1X|1S|1Y|E|$1Z|20|21|-4]]|22|$5|1X|1S|1Y|E|$1Z|23|21|-4]]|24|$5|1X|1S|1Y|E|$1Z|25|21|-4]]]]

Seymour's answer looks to be good, but I'm here to add a little that granularity matters in this scenario.

Assuming you have a star schema like <a href="https://i.stack.imgur.com/ecDWh.png" rel="nofollow noreferrer">this</a>, it is pretty straightforward you can define measures Total Forecast, Total Actual, Absolute Error, and Absolute Percentage Error with below formulas.

<pre><code>Total Forecast = SUM ( Forecast[Forecast] )
Total Actual = SUM ( Actual[Actual] )
Absolute Error = ABS ( [Total Forecast] - [Total Actual] )
Absolute Percentage Error = DIVIDE ( [Absolute Error], [Total Actual] )
</code></pre>

Here is what you will get so far.

<a href="https://i.stack.imgur.com/WqOH2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WqOH2.png" alt="Result 1"></a>

Here, you are asking how to calculate the sum of Absolute Percentage Errors.

By definition, Absolute Percentage Error shows the value of Absolute Error divided by Total Actual regardless of the drill-down level. Therefore at the grand total, it shows 0.52 which is Absolute Error (1,457.27) divided by Total Actual (2,786.27). If you want it to calculate differently in the grand total level, you need to explicitly implement this logic.

Your requirement would be stated more explicitly like below:

<ol>
<li>Calculate the values of Absolute Percentage Error in the granularity of each ItemName, Year, and Month.</li>
<li>And add them up.</li>
</ol>

The function you will need to implement this logic is <code>SUMX</code>. Also, you may explicitly use <code>SUMMARIZE</code> to make sure you are calculating Absolute Percentage Error in the specific granularity.

<pre><code>MAPEX = SUMX (
 SUMMARIZE (
 Forecast,
 'Product'[ItemName],
 'Calendar'[Year],
 'Calendar'[Month]
 ),
 [Absolute Percentage Error]
)
</code></pre>

<a href="https://i.stack.imgur.com/xhLzq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xhLzq.png" alt="Result 2"></a>

I have been emphasizing about the granularity so far. This is because if you are not conscious of the granularity, the result may look strange in some cases.

In the above image, MAPEX looks to be the same as Absolute Percentage Error except for the grand total. However, if you drill-down by Quarter instead of Month, you will notice it is not the same at all.

<a href="https://i.stack.imgur.com/bHwdh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bHwdh.png" alt="Result 3"></a>

Absolute Percentage Error is showing the quotient of Absolute Error and Total Actual at quarterly level, whereas MAPEX is still summing up monthly values of Absolute Percentage Error, even though Month is not being displayed in the table.

So, my final word is, whenever you invent a new measure like MAPEX, you always need to ask yourself if it makes sense or not for every possible granularities.

I'm fairly new to Power BI and struggling with an issue around totals in a table.

I am trying to calculate Mean Average Percentage Error (MAPE) using the following calculation:
[ABS(Actuals - Forecast)/Actuals]

Below is my dataset:

<a href="https://i.stack.imgur.com/l0lu7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/l0lu7.png" alt="MAPE_Dataset"></a>

The total in the 'MAPEX' Column is actually the sum of the totals in 'AbsErr' / 'Actuals' columns: (1457.27 / 2786.27 = 0.52).

What I actually need to show is the sum of the values in 'MAPEX' which totals 5.88.

The 'MAPEX' column is a Measure with the following definition:

<pre><code>MAPEX = DIVIDE([AbsErr], sum(CUBE_PeriodicData[Actuals]),0)
</code></pre>

I do not need to show the correct total in the 'Total' row in the table, it can be placed elsewhere in the report as a card, I would just like to know if there is a function in DAX that I am unaware of which will total the values in the column vertically?

Incorrect Totals in Power BI Table for MAPE

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我对Power BI相当陌生，我一直在努力解决表中的总计问题。我试图使用以下计算来计算平均百分比误差:/Actuals(Actuals-预测)下面是我的数据集：​​'MAPEX‘列中的总数实际上是'AbsErr’/ 'Actuals‘列中总数的总和：(1457.27 / 2786.27 = 0.52)。我真正需要显示的...

问MAPE电源BI表中的不正确总计
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MAPE电源BI表中的不正确总计EN