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

首次学习如何使用 Power Pivot 时，大多数用户发现真正的能力是采用某种方式聚合或计算结果。 如果数据有一个包含数值的列，则通过在数据透视表中或"字段列表"Power View它即可轻松进行聚合。 从本质上来说，由于它是数值，因此将自动进行求和、求平均值、计数或选择任何类型的聚合。 这称为隐式度量值。 隐式度量值非常适用于快速且简单的聚合，但它们具有限制，并且这些限制几乎总是可以通过显式度量值和计算列来克服。

让我们先看一个示例，其中，我们使用计算列为名为 Product 的表中的每一行添加新的文本值。 "产品"表中的每一行都包含有关我们销售的每个产品的所有信息。 我们有产品名称、颜色、大小、经销商价格等列。 我们有另一个名为 Product Category 的相关表，其中包含一列 ProductCategoryName。 我们希望产品表中的每个产品包含"产品类别"表中的产品类别名称。 在"产品"表中，我们可以创建名为"产品类别"的计算列，如下所示：

新的"产品类别"公式使用 RELATED DAX 函数从相关产品类别表中的 ProductCategoryName 列中获取值，然后输入产品表中每个产品 (行) 的值。

这是一个很好的示例，说明我们如何使用计算列为每一行添加 固定值，我们稍后可在数据透视表的"行、列"或"筛选器"区域或 Power View 报表使用。

让我们创建另一个示例，其中我们要计算利润率类别值。 这是一种常见方案，即使在许多教程中也一样。 数据模型中有一个"销售"表，该表包含交易数据，"销售"表与"产品类别"表存在关系。 在"销售额"表中，有一列包含销售额，另一列包含成本。

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

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

在这种情况下，Profit 仅作为 VALUES 中的字段有意义。 如果将 Profit 放在"列"区域中，数据透视表将如下所示：

"利润"字段放置在"列、行"或"筛选器"区域时，不会提供任何有用的信息。 它仅作为"值"区域中的聚合值有意义。

我们所做的是创建名为 Profit 的列，该列利润率 Sales 表中的每一行计算一个计算结果。 然后，我们将 Profit 添加到数据透视表的"值"区域，自动创建隐式度量值，其中会针对每个产品类别计算结果。 如果您认为我们确实计算了两次产品类别利润，则正确无误。 我们首先计算"销售额"表中的每一行利润，然后将"利润"添加到"值"区域，其中已针对每个产品类别聚合了利润。 如果您也认为我们实际上不需要创建 Profit 计算列，您也是正确的。 但是，我们如何在不创建 Profit 计算列的情况下计算利润？

利润确实会作为显式度量值进行更好的计算。

现在，我们将保留数据透视表的"销售"表和"产品类别"中的"利润"计算列，以及数据透视表的"值"中的"利润"，以比较结果。

在 Sales 表的计算区域中，我们将创建名为"总利润"的度量值 (以避免命名 冲突) 。 最后，它将产生与之前相同的结果，但没有 Profit 计算列。

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

然后，使用下面的公式创建另一个度量值：

总利润：=[ 总销售额Amount] - [COGS 总计]

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

可以看到，新的"总利润"度量值返回的结果与创建 Profit 计算列，然后将它放在 VALUES 中返回的结果相同。 区别是总利润度量值要高效，并且使数据模型更加简洁和精简，因为我们是在计算时，并且仅针对为数据透视表选择的字段进行计算。 我们实际上不需要 Profit 计算列。

为什么最后一部分很重要？ 计算列将数据添加到数据模型，数据占用内存。 如果刷新数据模型，则还需要处理资源才能重新计算"利润"列的所有值。 我们实际上不需要使用这样的资源，因为我们确实想要在数据透视表中选择要用于利润的字段（如产品类别、区域或日期）时计算利润。

让我们看看另一个示例。 其中计算列创建的结果一目了然正确，但...

本示例想要将销售额计算为总销售额的百分比。 我们在"销售额"表中创建名为"销售额 百 分比"的计算列，如下所示：

我们的公式指出：对于 Sales 表中的每一行，将 SalesAmount 列中的金额除以 SalesAmount 列中所有金额的 SUM 总计。

如果创建数据透视表，将"产品类别"添加到"列"，然后选择新的"销售额 百 分比"列以将其放入 VALUES 中，则我们会获得每个产品类别的总销售额百分比。

还行。 到目前为止，这看起来不错。 但是，让我们添加切片器。 添加"日历年"，然后选择一年。 在这种情况下，我们选择"2007"。 这是我们获得。

一目了然，这看起来可能仍然正确。 但是，我们的百分比应真正总计为 100%，因为我们想要了解 2007 年每个产品类别的总销售额百分比。 那么，出什么问题了？

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

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

还记得我们之前创建的 TotalSalesAmount 度量值吗，该度量值只是对 SalesAmount 列进行总和？ 我们在"总利润"度量值中使用它作为参数，并且我们将再次使用它作为新计算字段中的参数。

我们创建一个包含以下公式的新度量值：

总销售额的百分比：= ([Total SalesAmount]) / CALCULATE ([Total SalesAmount]， ALLSELECTED () )

此公式表示：将结果与"总销售额"除以 SalesAmount 的总和，除数据透视表中定义的列或行筛选器外，没有任何列或行筛选器。

现在，如果我们将新的总销售额 百 分比添加到数据透视表，则得到：

看起来更好。 现在 ，每个产品类别的 总销售额百分比计算为 2007 年总销售额的百分比。 如果我们在 CalendarYear 切片器中选择不同的年份或一年以上，我们会获得产品类别的新百分比，但我们的总计仍为 100%。 我们还可以添加其他切片器和筛选器。 无论应用任何切片器或筛选器，总销售额的百分比度量值始终都会产生总销售额的百分比。 使用度量值时，始终根据 COLUMNS 和 ROWS 中的字段确定的上下文以及应用的任何筛选器或切片器计算结果。 这是度量功能。

下面是一些指导，可帮助你确定计算列或度量值是否适合特定计算需求：

使用计算列

• 如果希望新数据显示在数据透视表的"行、列"或"筛选器"中，或者显示在 Power View 可视化效果中的 AXIS、LEGEND 或 TILE BY 上，则必须使用计算列。 与常规数据列一样，计算列可以用作任何区域中的字段，如果它们是数值，则还可以在 VALUES 中聚合计算列。

• 如果希望新数据成为行固定值数据。 例如，有一个日期表，其中包含一列日期，而您希望另一列只包含月份数。 您可以创建一个计算列，该计算列仅从"日期"列中的日期计算月份数字。 例如，=MONTH ('Date'[Date]) 。

• 如果要将每行的文本值添加到表中，请使用计算列。 具有文本值的字段不能聚合在 VALUES 中。 例如，=FORMAT ('Date'[Date]，"mmmm") 提供"日期"表中"日期"列中每个日期的月份名称。

使用度量值

• 如果计算结果始终依赖于在数据透视表中选择的其他字段。

• 如果需要执行更复杂的计算，例如基于某种类型的筛选器计算计数，或计算年度或方差，请使用计算字段。

• 如果要将工作簿的大小保持在最小，并最大程度地提高性能，请创建尽可能多的计算。 在许多情况下，所有计算都可以是度量值，从而显著减小工作簿大小并加快刷新时间。

请记住，创建计算列与使用"利润"列一样，然后在数据透视表或报告中聚合计算列没有任何错误。 实际上，它是一种了解和创建自己的计算的一种真正简单易用的方法。 随着对 Power Pivot 这两个极其强大的功能的理解不断增大，您需要创建最高效、最准确的数据模型。 希望你在此处学到的知识有所帮助。 还有其他一些真正出色的资源也可以帮助你。 下面是几个：DAX公式中的上下文、Power Pivot中的聚合和DAX资源汇。 尽管它更高级一些，面向会计和财务专业人员，但 Excel 示例中的"使用 Microsoft Power Pivot进行盈利和损失数据建模和分析"示例加载了出色的数据建模和公式示例。