如何将计算列的每一行除以另一计算列的总数? [英] How to divide each row of a calculated column by the total of another calculated column?
问题描述
我无法使用以下示例数据正确地进行除法:
I can't get a division correct with this sample data:
Calculated column Another calc. column
48 207
257 370
518 138
489 354
837 478
1,005 648
1,021 2,060
1,463 2,164
2,630 1,818
2,993 2,358
3,354 3,633
4,332 5,234
4,885 6,108
4,514 6,008
4,356 6,888
4,824 7,382
7,082 5,988
7,498 6,059
4,865
4,192
3,816
2,851
2,768
2,093
2,207
770
397
149
178
336
167
124
18
我要做的是创建一个新的
What I'm trying to do is to create a new calculated column.
对于每一行,我想获取计算列
的值并将其除以总计 另一个计算。列
。
For each row I want to get the value of Calculated column
and divide it by the Total of Another calc. column
.
的总和。列
= 82826
这是全新计算列中的期望输出,我们称之为%列
:
This is the desired output in a brand new calculated column, let's call it % Column
:
% Column
0,000579528167484
0,003102890396735
0,006254074807428
.
.
.
注意-这3列:
计算列
,另一个计算。列
和%列
都在同一表中,并且都是计算列。
NOTE - these 3 columns:
Calculated column
,Another calc. column
and% Column
are all in the same table and are all calculated columns.
我尝试了很多公式,但没有一个公式返回了所需的输出。 :|我猜这是由于计算列的性质而引起的,或者我没弄清要点。
I tried lots of formulas but not a single one returned the desired output. :| I guess it's because of the nature of calculated columns or I'm not getting the gist of it.
这是否有可能,或者我应该使用Measure走另一条路?
Is this even possible or I should follow another path using a Measure?
您能阐明一点吗?
#######编辑### ####
我整理了一个示例文件来帮助调试。在这里是:
https://drive.google.com/open?id= 1r7kiIkwgHnI5GUssJ6KlXBAoeDRISEuC
I put together a sample file to help debugging this. Here it is: https://drive.google.com/open?id=1r7kiIkwgHnI5GUssJ6KlXBAoeDRISEuC
如您所见:
-
每天获得的%硬编码
效果很好,因为82826
被硬编码为分母。 - <$当使用<$ c $时,StelioK 的c $ c>每日收益%和Alexis Olson 的
每日收益%为除法输出相同的错误值c> SUM
公式。
Earned Daily % HARDCODED
works just fine because82826
is hardcoded as the denominator.Earned Daily % by StelioK
andEarned Daily % by Alexis Olson
output the same wrong value for the division when usingSUM
formula.
如果要这样做,我正在使用最新的Power BI Desktop版本:版本:2.70.5494.701 64位(2019年6月)
I'm using the latest Power BI Desktop version if that matters: Version: 2.70.5494.701 64-bit (June 2019)
推荐答案
基本上,计算列没有问题,Alexis和S电话公式是正确的。
Basically, there is nothing wrong with the calculated columns, and both Alexis and StelioK formulas are correct.
这里的根本问题是计算列和度量之间的混淆。您正在以概念上错误的方式查看结果-通过矩阵可视化器,在切片器上启用了多个过滤器。如果删除筛选器,您将看到总数为140,920,而不是82,826。后一个数字是过滤后的数据集的总数,而不是整个表格的总数。
The root problem here is a confusion between calculated columns and measures. You are looking at the results in a conceptually wrong way - through the matrix visual, with several filters active on slicers. If you remove the filters, you will see that the total amount is 140,920, not 82,826. The latter number is the total for the filtered data set, not the entire table.
要正确使用此功能,您需要了解Power BI背后的几个基本概念:
To get this right, you need to understand several fundamental concepts behind Power BI:
- 计算的列始终是静态的。一旦计算完成
,它就无法响应切片器或其他UI控件。
只是静态数据,与未计算列中的数据相同。仅当创建
或重新加载数据时,用于计算列的DAX
公式才处于活动状态。 - 如果您希望计算对切片器等作出响应,则它们必须采取措施。这是唯一的方法,没有例外。
- 避免计算列,它们完全没有用。 Power BI就是有关措施的内容。我想不出使用计算列的单一原因。添加列时,实际上是在增强源数据,因为您感觉好像丢失了报表所需的内容。但是可以从源(您导入的数据库或文件)或使用完全针对此类任务设计的Power Query更好地解决该需求。最佳实践是:从头开始构建您的专栏,以进行其他所有设计措施。
- 另一个重要建议:切勿将字段(列)直接放在视觉效果中。始终编写DAX度量,然后再使用它。依靠Power BI自动聚合是非常糟糕的做法。
- Calculated columns are always static. Once a calculation is completed, it can not respond to slicers or other UI controls. It's just static data, identical to data in non-calculated columns. DAX formulas used to calculate columns are active only when you create them, or upon data reload.
- If you want your calculations to respond to slicers etc, they must be measures. It's the only way, no exceptions.
- Avoid calculated columns, they are utterly useless. Power BI is all about measures; I can't think of a single reason for using calculated columns. When you add a column, you are essentially enhancing your source data, because you feel like you are missing something you need for your report. But that need can be much better addressed at the source (database or file you import), or using Power Query, which is designed exactly for this kind of tasks. The best practice is: build your columns at the source, for everything else design measures.
- Another important advice: never drop fields (columns) into visuals directly. Always write a DAX measure, and then use it. Relying on Power BI auto-aggregations is a very bad practice.
这篇关于如何将计算列的每一行除以另一计算列的总数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!