Excel Power Pivot 简明教程

Excel Power Pivot - Basics of DAX

DAX (Data Analysis eXpression) 语言是 Power Pivot 的语言。DAX 由 Power Pivot 用于数据建模，它方便你用于自助 BI。DAX 是基于数据表和数据表中的列。请注意它不是基于表中的各个单元格，Excel 中的公式和函数就是这种情况。

你将学习数据模型中存在的两个简单计算 − 本章中的度量值列和度量值字段。

Calculated Column

度量值列是数据模型中的一列，由计算定义并扩展数据表的内容。它可视化为由公式定义的 Excel 表中的新列。

Extending the Data Model using Calculated Columns

假设你具有区域明智的产品销售数据以及数据模型中的产品目录。

extending data model

使用此数据创建 Power PivotTable。

data

正如你所看到的，Power PivotTable 已汇总了所有区域的销售数据。假设你要知道每种产品获得的总利润。你了解每种产品的价格、销售成本和已售出单位数。

gross profit

但是，如果你需要计算总利润，则需要在每个区域的数据表中再有两列 − 总产品价格和总利润。这是因为，数据透视图需要数据表中的列来汇总结果。

如你所知，总产品价格是产品价格 * 单位数，总利润是总金额 − 总产品价格。

你需要使用 DAX 表达式来添加度量值列，如下所示 −

  1. 单击 Power Pivot 窗口数据视图中的 East_Sales 选项卡以查看 East_Sales 数据表。

  2. 在功能区上点击“设计”选项卡。

  3. Click Add.

east sales tab

突出显示标题为 − 添加列的右侧列。

add column

在公式栏中键入 = [Product Price] * [No. of Units] 并按 Enter 。

formula bar

将插入一个标题为 CalculatedColumn1 的新列，其中包含由你输入的公式计算的值。

calculatedcolumn1

  1. 双击新度量值列的标题。

  2. 将标题重命名为 TotalProductPrice 。

totalproductprice

为总利润再添加一列度量值列，如下所示 −

  1. 在功能区上点击“设计”选项卡。

  2. Click Add.

  3. 突出显示标题为 − 添加列的右侧列。

  4. 在公式栏中键入 = [TotalSalesAmount] − [TotaProductPrice] 。

  5. Press Enter.

将插入一个标题为 CalculatedColumn1 的新列，其中包含由你输入的公式计算的值。

new column

  1. 双击新度量值列的标题。

  2. 将标题重命名为毛利。

header as gross profit

以同样的方式添加 North_Sales 数据表中的计算列。合并所有步骤，像如下进行-

  1. 在功能区上点击“设计”选项卡。

  2. 点击添加。右侧标题为-添加列的列被高亮。

  3. 在公式栏中输入= [Product Price] * [No. of Units] 并按下回车键。

  4. 由你输入的计算的值被插入到带有标题CalculatedColumn1的新列中。

  5. 双击新度量值列的标题。

  6. 将标题重命名为 TotalProductPrice 。

  7. 在功能区上点击“设计”选项卡。

  8. 点击添加。右侧标题为-添加列的列被高亮。

  9. 在公式栏中输入= [TotalSalesAmount] − [TotaProductPrice] 并按下回车键。由你输入的计算的值被插入到带有标题 CalculatedColumn1 的新列中。

  10. 双击新度量值列的标题。

  11. 将标题重命名为 Gross Profit 。

针对 South Sales 数据表和 West Sales 数据表重复上面给出的步骤。

north sales

你有必要的部分来汇总毛利。现在，创建动力透视表。

summarize gross profit

因为动力透视表中的计算列，你可以汇总 Gross Profit 且所有操作都可以在几个无差错步骤中完成。

你也可以按以下区域为产品进行汇总-

profit

Calculated Field

假设你想要按产品和地区计算利润百分比。你可以通过向数据表添加计算字段来执行此操作。

  1. 在动力透视表窗口中，点击 East_Sales 表中的毛利列下方。

  2. 在公式栏中输入 EastProfit: = SUM ([Gross Profit]) / sum ([TotalSalesAmount]) 。

  3. Press Enter.

eastprofit

计算字段EastProfit被插入到毛利列的下方。

  1. 右键点击计算字段-EastProfit。

  2. 从下拉列表中选择 Format 。

select format

格式设置对话框会出现。

  1. Select Number under Category.

  2. 在格式框中，选择百分比并单击确定。

select number

计算出的字段 EastProfit 被格式化为百分比。

percentage

重复这些步骤来插入以下计算出的字段 −

  1. North_Sales 数据表中的 NorthProfit。

  2. South_Sales 数据表中的 SouthProfit。

  3. West_Sales 数据表中的 WestProfit。

Note − 你不能为一个给定的名称定义多个计算出的字段。

单击 Power 透视表。你可以看到计算出的字段出现在表中。

repeat steps

  1. 从透视表字段列表中的表中选择字段 − EastProfit、NorthProfit、SouthProfit 和 WestProfit。

  2. 排列这些字段，以使毛利润和百分比利润同时出现。Power 透视表如下所示 −

percentage profit

Note − 在 Excel 较早版本中， Calculate Fields 被称为 Measures 。