在数据透视表或数据透视图中创建度量值

项目
09/10/2013

度量值是为度量与分析有关的其他因素（如时间、地理、组织或产品特征）相关的结果创建的计算。有几种创建度量值的方法，但根据您使用此度量值的计划，并不是所有的方法都同等有效。具体来说，如果您创建一个工作簿以用作报表应用程序中的数据模型，则要确保仅创建显式度量值，如下面各节所述。

创建用于数据模型的度量值

在 PowerPivot 工作簿中创建的度量值可用于您在 Power View 或其他报表应用程序中生成的报表，并与您在报表中创建的其他度量值或计算共存。为了使度量值作为预定义算法出现在该报表中，您必须在 PowerPivot 工作簿中将其创建为一个显式度量值。显式度量值是一个手动创建的度量值。它与隐式度量值不同，后者是在将字段添加到数据透视表的“值”区域中时由 Excel 生成的。

创建用于 Excel 的度量值

在创建用于 Excel 的度量值时，您必须首先将数据透视表或数据透视图添加到您的 PowerPivot 工作簿中。然后，您可以使用以下任意方法创建度量值：

通过将字段从 PowerPivot 字段列表拖动到“值”区域来创建隐式度量值。如果您拖动一个数字字段，则通过使用 SUM 聚合计算隐式度量值。如果您拖动文本字段，则通过使用 COUNT 聚合计算该度量值。您可以编辑隐式度量值，以将该计算更改为使用不同聚合，如 MIN、MAX 或 DISTINCTCOUNT。

注意
隐式度量值易于创建，但比显式度量值具有更多限制。隐式度量值无法重命名、移动或用于工作簿中的其他数据透视表或数据透视图。此外，因为隐式度量值基于现有字段，所以在删除该字段时，也将删除相关的隐式度量值。最后，隐式度量值只能使用内置于聚合中的数据格式；它们不支持可用于显式度量值的各种数据格式。

隐式度量值易于创建，但比显式度量值具有更多限制。隐式度量值无法重命名、移动或用于工作簿中的其他数据透视表或数据透视图。此外，因为隐式度量值基于现有字段，所以在删除该字段时，也将删除相关的隐式度量值。最后，隐式度量值只能使用内置于聚合中的数据格式；它们不支持可用于显式度量值的各种数据格式。

通过使用 PowerPivot 功能区中的**“新建度量值”**按钮，手动创建显式度量值。
通过将度量值名称和公式键入到单元的公式区域，在 PowerPivot 窗口的**“计算区域”**中手动创建显式度量值。

在添加度量值时，对数据透视表的**“值”**区域中的每个单元计算该公式。因为会为每个行和列标题组合都创建一个结果，所以度量值的结果在每个单元中可能会不同。

示例：创建使用简单聚合的显式度量值

此示例包括来自 AdventureWorks 数据库的基于自行车的数据。有关从哪里获取示例工作簿的信息，请参阅获取 PowerPivot 示例数据。有关公式的详细信息，请参阅生成计算的公式。

该示例演示了两种创建显式度量值的方法。第一种方法，您将在 PowerPivot 窗口的“计算”区域（用于显示模型中定义的所有度量值）中创建一个度量值。第二种方法，您将某一数据透视表或数据透视图添加到您的 PowerPivot 工作簿后，然后使用**“度量值设置”**对话框添加度量值。度量值的公式定义求和、取平均值或使用 PowerPivot 工作簿中的列和表的其他计算。

在 PowerPivot 窗口中单击**“主文件夹”选项卡，然后在“查看”组中单击“计算区域”**。
在 FactResellerSales 表中，在计算区域中的任何位置单击一个单元。
在位于工作簿顶部的编辑栏中，在格式 <measurename>:<formula> 中输入公式：
```
Projected Sales:=SUM('FactResellerSales'[SalesAmount])*1.06
```
单击**“确定”**接受该公式。
在 PowerPivot 窗口中单击**“主文件夹”选项卡，然后在“报表”组中单击“数据透视表”**。

在**“创建数据透视表”对话框中，确认已选中“新建工作表”，然后单击“确定”**。

PowerPivot 在新的 Excel 工作表中创建一个空的数据透视表，并且在工作簿的右侧显示 PowerPivot 字段列表。
展开 FactResellerSales 表，以查看刚刚创建的度量值。如果在创建度量值之前工作簿中已具有一个数据透视表，则必须单击位于 PowerPivot 字段列表顶部的“刷新”按钮以刷新字段。
在 Excel 窗口中，在 PowerPivot 选项卡的**“度量值”组中，单击“新建度量值”**。
在**“度量值设置”对话框中，对于“表名”**单击向下箭头，然后从下拉列表中选择 FactResellerSales。

所选的表确定将存储度量值定义的位置。度量值无需与该度量值引用的表一起存储。
对于**“度量值名称(所有数据透视表)”**，键入 Total Quantity。
度量值的名称在工作簿内必须唯一，并且您不能使用用于工作簿中任何列的相同名称。
在**“公式”**文本框中，将光标放置在等号 (=) 后，然后输入以下公式：
```
SUM(FactResellerSales[OrderQuantity])
```
单击**“确定”**。

您创建的两个度量值都将随源数据表一起保存，但可以用于任何数据透视表或数据透视图。这两个度量值将出现在 PowerPivot 字段列表中并且可供工作簿的所有用户使用。

示例：创建使用自定义聚合的显式度量值

在这个示例中，您将创建一个自定义聚合，该聚合使用新的 DAX 聚合函数之一 SUMX；并且还使用函数 ALL。在这个例子中，该函数返回某个列中的所有值，而与该列的上下文无关。该示例使用来自 DAX 示例工作簿的以下几列：

DateTime[CalendarYear]
ProductCategory[ProductCategoryName]
ResellerSales_USD[SalesAmount_USD]

该示例使用一个数据透视表，该数据透视表具有 CalendarYear 作为行标签，具有 ProductCategoryName 作为列标签；SalesAmount_USD 用于度量值公式中。该示例回答的问题是：每年每个产品类别的销售额占从 2005 年到 2008 年的总销售额的多少百分比？例如，这使您可以看到自行车销售额在 2007 年占总销售额的百分比。为了回答此问题，我们使用以下度量值公式：

=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])

按如下所示构造公式：

分子 SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD]) 是数据透视表中当前单元的 ResellerSales_USD[SalesAmount_USD] 中各值的总和。具有 CalendarYear 和 ProductCategoryName 的上下文意味着该值对于年份和产品类别的每个组合将是不同的。例如，在 2003 年售出的自行车的总数不同于在 2008 年售出的附件的总数。
对于分母，可以通过指定表 ResellerSales_USD 来开始，并且使用 ALL 函数来删除该表上的所有上下文。这确保该值对于年份和产品类别的每个组合将是相同的：分母将始终是从 2005 年到 2008 年的总销售额。
然后，您使用 SUMX 函数计算 ResellerSales_USD[SalesAmount_USD] 列中各值的和。换言之，获取所有分销商销售额的 ResellerSales_USD[SalesAmount_USD] 之和。

注意
在 Windows Vista 和 Windows 7 中，PowerPivot 窗口中的功能可从功能区中访问，本主题将对此进行讨论。在 Windows XP 中，这些功能是从一组菜单中访问的。如果您使用的是 Windows XP，并且希望了解菜单命令如何与功能区命令相关，请参阅 Windows XP 中的 PowerPivot 用户界面。

创建使用自定义聚合的度量值

在 PowerPivot 窗口中单击**“主文件夹”选项卡，然后在“报表”组中单击“数据透视表”**。
在**“创建数据透视表”对话框中，确认已选中“新建工作表”，然后单击“确定”**。

PowerPivot 在新的 Excel 工作表中创建一个空的数据透视表，并且在工作簿的右侧显示 PowerPivot 字段列表。
在 Excel 窗口中，使用**“PowerPivot 字段列表”**可以将列添加到数据透视表：
1. 找到 DateTime 表，并且将列 CalendarYear 拖到数据透视表的**“行标签”**区域。
2. 找到 ProductCategory 表，并且将列 ProductCategoryName 拖到数据透视表的**“行标签”**。
在 Excel 窗口中，在 PowerPivot 选项卡的**“度量值”组中，单击“新建度量值”**。
在**“度量值设置”对话框中，对于“表名”**单击向下箭头，然后从下拉列表中选择 ResellerSales_USD。

所选的表确定将存储度量值定义的位置。度量值无需与该度量值引用的表一起存储。
对于**“度量值名称(所有数据透视表)”**，键入 AllResSalesRatio。

此名称用作度量值的标识符；因此，它在工作簿中必须唯一，并且不能更改。
对于**“自定义名称(此数据透视表)”**，键入 All Reseller Sales Ratio。

此名称仅在当前数据透视表中使用，用于显示目的。例如，您可能在其他数据透视表中重用度量值 AllResSalesRatio，但是给它取个其他名称或用在其他语言中。
在**“公式”**文本框中，将光标放置在等号 (=) 后。
键入 SUMX，然后键入一个括号。
```
=SUMX( 
```
在您键入时，**“公式”**文本框下的工具提示将指示该 SUMX 函数需要两个参数：第一个参数是一个表或者返回表的表达式，第二个参数是提供可求和的数字的表达式。

键入 Res，然后从列表中选择 ResellerSales_USD 并按下 Tab 键。

列名将插入到公式中，如下所示：
```
=SUMX(ResellerSales_USD
```
键入一个逗号。

工具提示将更新以显示所需的下一个参数是 expression。表达式可以是一个值、对列的引用或者它们的某种组合。例如，您可以创建对其他两列求和的表达式。对于此示例，您将提供包含每个分销商的销售额的列的名称。
键入表名称的前几个字母，该表包含您要包含的列。对于此示例，键入 Res，然后从列表中选择 ResellerSales_USD[SalesAmount_USD] 列。
按 Tab 键以便将该列名称插入到公式中并添加右括号，如下所示：
```
=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])
```
键入正斜杠，然后键入以下代码或者将以下代码复制并粘贴到**“度量值设置”**对话框中：
```
SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])
```
请注意 ALL 函数在 SUMX 函数内是如何嵌套的。整个公式现在应如下所示：
```
=SUMX(ResellerSales_USD, ResellerSales_USD[SalesAmount_USD])/SUMX(ALL(ResellerSales_USD), ResellerSales_USD[SalesAmount_USD])
```
单击**“检查公式”**。

检查公式的语法或引用错误。更正发现的所有错误，如缺少括号或逗号。
单击**“确定”**。

度量值现在使用日历年度和产品类别的每个组合的值填充数据透视表。
设置表的格式：
1. 选择数据透视表中的数据，包括**“总计”**行。
2. 在**“主文件夹”选项卡的“编号”组中，单击百分比按钮 (%) 一次，然后单击增加小数位数按钮 (<- .0 .00**) 两次。
完成的表将如下所示。您现在可以看到产品和年度的每个组合占总销售额的百分比。例如，自行车在 2007 年的销售额占从 2005 年到 2008 年的总销售额的 31.71%。

所有分销商销售额	列标签
行标签	Accessories	Bikes	Clothing	组件	Grand Total
2005	0.02%	9.10%	0.04%	0.75%	9.91%
2006	0.11%	24.71%	0.60%	4.48%	29.90%
2007	0.36%	31.71%	1.07%	6.79%	39.93%
2008	0.20%	16.95%	0.48%	2.63%	20.26%
Grand Total	0.70%	82.47%	2.18%	14.65%	100.00%

请参阅

通过