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

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

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

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

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

让我们创建另一个示例，在其中计算产品类别的利润率。 这是一种常见方案，即使在许多教程中也是如此。 数据模型中有一个包含事务数据的 Sales 表，并且 Sales 表与产品类别表之间存在关系。 在 Sales 表中，我们有一个包含销售金额的列，另一个包含成本的列。

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

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

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

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

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

利润，真的会更好地计算为一个明确的度量值。

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

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

首先，在“Sales”表中，选择“SalesAmount”列，然后单击“自动求和”以创建显式 SalesAmount总和度量值。 请记住，显式度量值是在 Power Pivot 中表的计算区域中创建的度量值。 我们对 COGS 列也执行了相同的操作。 我们将重命名这些 Total SalesAmount 和 Total COGS ，使其更易于识别。

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

总利润：=[ Total SalesAmount] - [Total COGS]

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

可以看到，我们新的“总利润”度量值返回的结果与创建利润计算列，然后将其置于 VALUES 中相同。 区别在于，“总利润”度量值的效率要高得多，并且使得数据模型更简洁，因为我们当时正在计算，并且仅针对为数据透视表选择的字段进行计算。 我们实际上不需要利润计算列。

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

让我们看一下另一个示例。 计算列会创建乍一看看起来正确的结果，但...

在此示例中，我们希望将销售额计算为总销售额的百分比。 我们在 Sales 表中创建名为 “销售额百分比”的 计算列，如下所示：

公式状态：对于 Sales 表中的每一行，将 SalesAmount 列中的金额除以 SalesAmount 列中所有金额的总和。

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

还行。 到目前为止，这看起来不错。 但是，让我们添加一个切片器。 添加“日历年”，然后选择一年。 在本例中，我们选择“2007”。 这就是我们得到的。

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

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

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

还记得我们之前创建的 TotalSalesAmount 度量值，即对 SalesAmount 列进行求和的度量值吗？ 我们在“总利润”度量值中将其用作参数，并将它再次用作新计算字段中的参数。

使用以下公式创建新度量值：

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

此公式状态：将结果与 Total SalesAmount 除以 SalesAmount 的总和，而不使用数据透视表中定义的筛选器以外的任何列或行筛选器。

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

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

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

使用计算列

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

• 如果希望新数据是行的固定值。 例如，你有一个包含日期列的日期表，并且需要另一个仅包含月份数的列。 可以创建一个计算列，该列仅计算“日期”列中的日期的月份数。 例如，=MONTH ('Date'[Date]) 。

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

使用度量值

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

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

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

请记住，创建计算列与使用利润列一样，然后将其聚合到数据透视表或报表中没有任何问题。 这实际上是了解和创建自己的计算的一种非常好且简单的方法。 随着你对 Power Pivot 这两个极其强大的功能的了解不断加深，你将希望创建尽可能高效、最准确的数据模型。 希望你在这里学到的东西有所帮助。 还有其他一些非常出色的资源也可以帮助你。 下面是几个：DAX 公式中的上下文、Power Pivot 中的聚合和 DAX 资源汇。 此外，虽然它更高级一些，面向会计和金融专业人员，但 Excel 中 Microsoft Power Pivot 的损益数据建模和分析 示例加载了出色的数据建模和公式示例。