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

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

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

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

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

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

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

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

在这种情况下，Profit 仅作为 VALUES 中的一个字段有意义。 如果我们将利润放在 COLUMNS 区域中，数据透视表将如下所示：

当我们的利润字段放置在 COLUMNS、ROWS 或 FILTERS 区域时，它不会提供任何有用的信息。 它仅作为 VALUES 区域中的聚合值有意义。

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

利润，确实最好作为一种明确的衡量标准来计算。

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

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

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

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

Total Profit：=[Total SalesAmount] - [Total COGS]

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

可以看到，我们的新“总利润”度量返回的结果与创建“利润”计算列并将其置于 VALUES 中时相同。 不同之处在于，我们的“总利润”度量值效率要高得多，并且使我们的数据模型更干净、更精简，因为我们在时间进行计算，并且只针对我们为数据透视表选择的字段。 毕竟，我们真的不需要利润计算列。

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

我们来看另一个例子。 计算列创建的结果乍一看是正确的，但是......

在此示例中，我们要计算销售额占总销售额的百分比。 我们在 Sales 表中创建一个名为 % of Sales 的计算列，如下所示：

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

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

好的。 到目前为止，这看起来不错。 但是，让我们添加一个切片器。 我们添加 Calendar Year，然后选择年份。 在本例中，我们选择 2007。 这就是我们得到的。

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

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

我们可以删除“销售额百分比”计算列，因为这对我们没有帮助。 相反，我们将创建一个度量值，无论应用了何种筛选器或切片器，都能正确计算占总销售额的百分比。

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

我们使用以下公式新建一个度量值：

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

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

现在，如果我们将新的 % of Total Sales 添加到 数据透视表，则会得到：

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

下面是一些准则，可帮助您确定计算列或度量是否适合特定的计算需求：

使用计算列

• 如果希望新数据显示在数据透视表的行、列或筛选器中，或 Power View 可视化效果中的“轴”、“图例”或“平铺依据”上，必须使用计算列。 就像常规数据列一样，计算列可以用作任何区域的字段，如果它们是数值，它们也可以汇总在 VALUES 中。
• 如果希望新数据是行的固定值。 例如，您有一个带有日期列的日期表，而您希望另一列仅包含月份的数量。 可以创建一个计算列，根据“日期”列中的日期仅计算月份数。 例如，=MONTH ('Date'[Date]) 。
• 如果要为表中的每一行添加文本值，请使用计算列。 包含文本值的字段永远不能汇总到 VALUES 中。 例如，=FORMAT ('Date'[Date]，“mmmm”) 为 Date 表的“日期”列中的每个日期提供月份名称。

使用度量值

• 如果计算结果始终取决于在数据透视表中选择的其他字段。
• 如果需要执行更复杂的计算（例如基于某种筛选器计算计数，或计算同比或方差），请使用计算字段。
• 如果要将工作簿的大小保持在最小值并最大限度其性能，请创建尽可能多的计算作为度量值。 在许多情况下，所有计算都可以是度量值，从而显著减小工作簿大小并加快刷新时间。

请记住，像创建利润列一样创建计算列，然后将其聚合到数据透视表或报表中并无问题。 这实际上是一种学习和创建自己的计算的好方法。 随着对 PowerPivot 这两项极其强大的功能的了解不断加深，你将需要创建尽可能高效、最准确的数据模型。 希望你在这里学到的内容对你有所帮助。 还有其他一些非常好的资源也可以为您提供帮助。 此处仅部分提供：DAX 公式中的上下文、Power Pivot 中的聚合和 DAX 资源汇。 而且，虽然它稍微高级一些，并且面向会计和财务专业人员，但“ 在 Excel 中使用 Microsoft Power Pivot 进行损益数据建模和分析 ”示例加载了出色的数据建模和公式示例。