何时使用计算列和计算字段

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。本页面是自动翻译的,可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。可以在本页面底部告诉我们此信息是否对你有帮助吗?请在此处查看本文的英文版本以便参考。

当第一次学习如何使用 Power Pivot 时, 大多数用户发现真正的威力是以某种方式聚合或计算结果。如果数据包含具有数值的列, 则可以通过在数据透视表或 Power View 字段列表中进行选择来轻松地聚合数据。本质上, 因为它是数字, 所以它将自动进行求和、求平均值、计数或您选择的任何类型的聚合。这称为隐式度量。隐式度量非常适用于快速而简单的聚合, 但它们具有限制, 几乎始终可以使用显式度量值计算列来解决这些限制。

首先我们来看看一个示例, 在该示例中, 我们使用计算列为名为 Product 的表中的每一行添加新的文本值。Product 表中的每一行包含我们销售的每种产品的各种信息。我们提供了产品名称、颜色、尺寸、经销商价格等的列。我们还有一个名为 "产品类别" 的其他相关表, 其中包含列 ProductCategoryName。我们想要为产品表中的每个产品包含产品类别表中的产品类别名称。在我们的产品表中, 我们可以创建名为 "产品类别" 的计算列, 如下所示:

产品类别计算列

我们的新产品类别公式使用相关的 DAX 函数从相关产品类别表的 ProductCategoryName 列中获取值, 然后在产品表中为每个产品 (每一行) 输入相应的值。

这是一个很好的示例, 我们可以使用计算列来为每个行添加固定值, 以便以后可以在数据透视表或 Power View 报表中的行、列或筛选区域中使用。

让我们再创建一个示例, 在该示例中, 我们想要计算产品类别的利润率。这是一种常见方案, 即使在许多教程中也是如此。我们的数据模型中有一个包含交易数据的 sales 表, "销售" 表和 "产品" 类别表之间存在关系。在 "销售" 表中, 我们有一个包含销售额的列和一个具有成本的另一列。

我们可以创建一个计算列, 通过在 "SalesAmount" 列的值中减去 "COGS" 列中的值来计算每行的利润金额, 如下所示:

Power Pivot 表中的“利润”列

现在, 我们可以创建数据透视表并将 "产品类别" 字段拖到 "列", 将新的 "利润" 字段拖到 "值" 区域中 (在 PowerPivot 中的表中的列是 "数据透视表字段列表" 中的字段)。结果是一个名为 "利润总和" 的隐式度量值。它是每个不同产品类别的 "利润" 列中的值的聚合量。我们的结果如下所示:

简单的数据透视表

在这种情况下, "利润" 只是值中的字段有意义的。如果我们要将利润放在 "列" 区域中, 则数据透视表的外观将如下所示:

不具有有用值的数据透视表

将 "利润" 域放在列、行或筛选区域中时, 不会提供任何有用的信息。它仅在 "值" 区域中具有聚合值的意义。

我们所做的是创建一个名为 "利润" 的列, 用于计算 Sales 表中每一行的利润率。然后, 我们将利润增加到数据透视表的 "值" 区域, 自动创建隐式度量, 其中为每个产品类别计算结果。如果你认为我们的产品类别的利润是两次, 则是正确的。首先为 Sales 表中的每一行计算一个利润, 然后向值区域添加利润, 以便对每个产品类别进行聚合。如果你还认为我们实际上不需要创建利润计算列, 那么你也是正确的。但是, 我们如何在不创建利润计算列的情况下计算利润?

利润将真正以明确的度量值的形式得到更好的计算。

现在, 我们将把利润计算列放在 "销售额表" 和 "产品" 类别中, 在 "数据透视表" 中的 "列" 和 "利润" 的值之间进行比较, 以比较结果。

在我们的 "销售额" 表的计算区域中, 我们将创建一个名为 "总利润" 的度量值 (以避免命名冲突)。最后, 它将产生与我们以前所做的相同的结果, 但没有利润计算列。

首先, 在 "销售额" 表中, 选择 "SalesAmount" 列, 然后单击 "自动求和" 以创建明确的 SalesAmount度量总和。请记住, 明确的度量值是我们在 Power Pivot 中的表的计算区域中创建的一个。我们对 "COGS" 列执行相同的操作。我们将对这些SalesAmount总计值进行重命名, 以使其更易于识别。

Power Pivot 中的“自动求和”按钮

然后, 我们通过此公式创建另一个度量值:

总利润: = [ 总 SalesAmount]-[总成本总额 ]

注意: 我们还可以编写公式为总利润: = SUM ([SalesAmount])-SUM ([COGS]), 但通过创建单独的总 SalesAmount 和总成本度量值, 我们也可以在数据透视表中使用它们, 也可以将它们用作所有类型的其他度量值公式中的参数。

将新的总利润度量值更改为货币格式后, 可以将其添加到数据透视表中。

数据透视表

你可以看到新的总利润度量值返回的结果与创建利润计算列相同, 然后将其放入值中。区别是我们的总利润衡量值远更高效, 并使我们的数据模型更整洁, 并更简洁, 因为我们将在时间进行计算, 并且仅对我们为数据透视表选择的字段进行计算。我们实际上并不需要该利润计算列。

为什么最后一部分是重要的?计算列将数据添加到数据模型, 并且数据占用内存。如果刷新数据模型, 则还需要处理资源来重新计算 "利润" 列中的所有值。我们实际上不需要占据这样的资源, 因为我们需要在数据透视表中选择要对其进行利润的字段 (如产品类别、区域或日期) 来计算利润。

我们来看另一个示例。一个计算列在第一眼看上去会创建结果正确, 但 ...。

在此示例中, 我们希望将销售额计算为总销售额的百分比。我们在 sales 表中创建名为 "(销售额百分比) 的计算列, 如下所示:

销售额百分比计算列

我们的公式状态: 对于 Sales 表中的每一行, 将 "SalesAmount" 列中的金额除以 "SalesAmount" 列中所有金额的总和。

如果创建数据透视表并向列添加产品类别, 然后选择新的 "销售额" 列以将其放入值中, 我们将获得每个产品类别的销售额的总金额的总和。

数据透视表显示产品类别的销售百分比之和

确定.到目前为止, 这看起来很好。不过, 我们来添加切片器。我们将添加 "日历年", 然后选择一年。在这种情况下, 我们选择2007。这就是我们获得的内容。

数据透视表中的销售百分比之和不正确结果

乍一看, 这可能仍然是正确的。但是, 我们的百分比应该真正 100% 的总和, 因为我们想要了解我们每个产品类别 for 2007 的总销售额的百分比。什么也出现了问题?

我们的 "销售额百分比" 列为 "SalesAmount" 列中的值除以 "SalesAmount" 列中所有值的总计之和。计算列中的值是固定的。对于表中的每一行, 它们是不可变的结果。当我们向数据透视表添加销售额的百分比时, 它将被聚合为 SalesAmount 列中所有值的总和。"销售额百分比" 列中的所有值之和将始终为 100%。

提示: 请务必阅读DAX 公式中的上下文。它提供了对行级别上下文和筛选器上下文的良好理解, 这正是我们在此处介绍的内容。

我们可以删除销售额计算列的百分比, 因为它不会帮助我们。取而代之的是, 我们将创建一个可正确计算总销售额百分比的度量值, 而不考虑所应用的任何筛选器或切片器。

请记住我们以前创建的 TotalSalesAmount 度量值, 即只是对 SalesAmount 列求和的那种度量值?我们将其用作总利润度量值中的参数, 我们将在新的计算字段中再次使用它作为参数。

提示: 在数据透视表或报表中创建明确的度量值 (例如总 SalesAmount 和总 COGS) 不仅十分有用, 而且当你需要结果作为参数时, 它们也可用作其他度量值中的参数。这使你的公式更高效, 更易于阅读。这是一种良好的数据建模做法。

我们使用以下公式创建一个新的度量值:

总销售额的百分比: = ([总 SalesAmount])/计算 ([total SalesAmount], ALLSELECTED ())

此公式状态: 除数据透视表中未定义的列或行筛选器外, 将总计 SalesAmount 的结果除以 SalesAmount 的总和。

提示: 请务必阅读有关 DAX 参考中的计算ALLSELECTED函数的信息。

现在, 如果我们将 "总销售额" 的新百分比添加到数据透视表, 我们将获得:

数据透视表中的“销售百分比总和”的 正确结果

这样看起来更好。现在, 每个产品类别的总销售额的百分比计算为2007年的总销售额的百分比。如果在 CalendarYear 切片器中选择其他年份或多个年份, 我们将为产品类别获取新的百分比, 但我们的总计仍为 100%。我们还可以添加其他切片器和筛选器。总销售额度量的百分比将始终生成总销售额的百分比, 而不考虑应用的任何切片器或筛选器。使用度量值时, 将始终根据由列和行中的字段以及所应用的任何筛选器或切片器确定的上下文计算结果。这是度量值的威力。

以下是一些指导原则, 可在决定计算列或度量值是否适合特定计算需求时提供帮助:

使用计算列

  • 如果希望新数据显示在数据透视表中的行、列或筛选器中, 或者在轴、图例上或在 Power View 可视化对象中的图块上显示, 则必须使用计算列。就像数据的常规列一样, 计算列可以用作任何区域中的字段, 如果它们是数字, 也可以将它们聚合到值中。

  • 如果希望新数据为行的固定值。例如, 您有一个包含日期列的日期表, 并且您想要只包含月份的编号的另一列。您可以创建一个计算列, 它仅计算日期列中日期的月份数。例如, = MONTH ("date" [date])。

  • 如果要向表中的每一行添加文本值, 请使用计算列。具有文本值的字段永远不能在值中聚合。例如, = FORMAT ("date" [date], "mmmm") 为我们提供了日期表中日期列中每个日期的月份名称。

使用度量值

  • 如果计算结果始终依赖于您在数据透视表中选择的其他字段, 则会出现这种情况。

  • 如果需要执行更复杂的计算, 例如根据某种类型的筛选计算计数, 或计算年或方差, 请使用计算字段。

  • 如果你想要保持你的工作簿的大小最小并最大化其性能, 请创建尽可能多的计算作为度量值。在许多情况下, 你的所有计算都可以度量, 显著降低工作簿大小和加快刷新时间。

请记住, 创建计算列没有什么不妥之处, 如我们使用利润列, 然后将其聚合到数据透视表或报表中。它实际上是一种了解和创建自己的计算的非常好且简便的方法。当你理解这两种强大的 Power Pivot 功能, 你将需要创建最高效、最准确的数据模型。希望你在此处了解到的内容有所帮助。还有一些其他真正出色的资源可以帮助你。下面只是一些内容: DAX 公式中的上下文Power Pivot 中的聚合以及DAX 资源中心。而且, 虽然是更高级的, 并且向会计和财务专业人士发送, 但通过使用出色的数据建模和公式示例加载了有关Microsoft Power Pivot Excel 示例中的利润和损失数据建模和分析

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划