如何将计算列的每一行除以另一个计算列的总数? [英] How to divide each row of a calculated column by the total of another calculated column?

问题描述

我无法用这个样本数据得到正确的除法:

计算列 另一种计算.柱子48 207257 370518 138489 354837 4781,005 6481,021 2,0601,463 2,1642,630 1,8182,993 2,3583,354 3,6334,332 5,2344,885 6,1084,514 6,0084,356 6,8884,824 7,3827,082 5,9887,498 6,0594,8654,1923,8162,8512,7682,0932,20777039714917833616712418

我要做的是创建一个新的计算列.

对于每一行，我想获取 Calculated column 的值并将其除以 Another calc 的总计.列.

另一个计算的总数.列 = 82826

这是一个全新的计算列中所需的输出，我们称之为% Column:

% 列0,0005795281674840,0031028903967350,006254074807428...

<块引用>

注意 - 这 3 列:计算列，另一个计算.column 和 % Column 都在同一张表中，都是计算列.

我尝试了很多公式，但没有一个返回所需的输出.:|我想这是因为计算列的性质，或者我没有理解它的要点.

这是否可能，或者我应该使用 Measure 遵循另一条路径?

你能解释一下吗?

####### 编辑#######

我整理了一个示例文件来帮助调试它.这里是:

解决方案

基本上计算出来的列没有问题，Alexis和StelioK的公式都是正确的.

这里的根本问题是计算列和度量之间的混淆.您正在以一种概念上错误的方式查看结果 - 通过矩阵视觉效果，切片器上有几个过滤器处于活动状态.如果您删除过滤器，您将看到总量为 140,920，而不是 82,826.后一个数字是过滤数据集的总数，而不是整个表格.

要做到这一点，您需要了解 Power BI 背后的几个基本概念:

• 计算的列始终是静态的.一旦计算完成完成后，它无法响应切片器或其他 UI 控件.它是只是静态数据，与非计算列中的数据相同.达克斯用于计算列的公式仅在您创建时有效它们，或在数据重新加载时.
• 如果您希望您的计算响应切片器等，它们必须是度量.这是唯一的方法，没有例外.
• 避免使用计算列，它们完全没用.Power BI 是关于度量的；我想不出使用计算列的单一原因.添加列时，您实际上是在增强源数据，因为您感觉缺少报告所需的内容.但是，可以在源(数据库或您导入的文件)或使用专为此类任务设计的 Power Query 更好地满足这种需求.最佳实践是:在源头构建您的列，为其他一切设计措施.
• 另一个重要建议:切勿将字段(列)直接放入视觉对象中.始终编​​写 DAX 度量，然后使用它.依赖 Power BI 自动聚合是一种非常糟糕的做法.

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.

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
.
.
.

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.

Is this even possible or I should follow another path using a Measure?

Can you shed some light?

####### EDIT #######

I put together a sample file to help debugging this. Here it is: https://drive.google.com/open?id=1r7kiIkwgHnI5GUssJ6KlXBAoeDRISEuC

As you see:

• Earned Daily % HARDCODED works just fine because 82826 is hardcoded as the denominator.
• Earned Daily % by StelioK and Earned Daily % by Alexis Olson output the same wrong value for the division when using SUM formula.

I'm using the latest Power BI Desktop version if that matters: Version: 2.70.5494.701 64-bit (June 2019)

解决方案

Basically, there is nothing wrong with the calculated columns, and both Alexis and StelioK formulas are correct.

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.

To get this right, you need to understand several fundamental concepts behind 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屋！