Excel Power Pivot 简明教程

Excel Power Pivot - Basics of DAX

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

DAX (Data Analysis eXpression) language is the language of Power Pivot. DAX is used by Power Pivot for data modeling and it is convenient for you to use for self-service BI. DAX is based on data tables and columns in data tables. Note that it is not based on individual cells in the table as is the case with the formulas and functions in Excel.

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

You will learn the two simple calculations that exist in Data Model − Calculated Column and Calculated Field in this chapter.

Calculated Column

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

Calculated column is a column in the Data Model that is defined by a calculation and that extends the content of a data table. It can be visualized as a new column in an Excel table defined by a formula.

Extending the Data Model using Calculated Columns

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

Suppose you have sales data of products region-wise in data tables and also a Product Catalog in the Data Model.

extending data model

使用此数据创建 Power PivotTable。

Create a Power PivotTable with this data.

data

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

As you can observe, the Power PivotTable has summarized the sales data from all the regions. Suppose you want to know the gross profit made on each of the products. You know the price of each product, the cost at which it is sold and the number of units sold.

gross profit

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

However, if you need to calculate the gross profit, you need to have two more columns in each of the data tables of the regions − Total Product Price and Gross Profit. This is because, PivotTable requires columns in data tables to summarize the results.

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

As you know, Total Product Price is Product Price * No. of Units and Gross Profit is Total Amount − Total Product Price.

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

You need to use DAX Expressions to add the Calculated Columns as follows −

  1. Click the East_Sales tab in Data View of the Power Pivot window to view the East_Sales Data Table.

  2. Click the Design tab on the Ribbon.

  3. Click Add.

east sales tab

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

The column on the right side with the header − Add Column is highlighted.

add column

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

Type = [Product Price] * [No. of Units] in the formula bar and press Enter.

formula bar

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

A new column with header CalculatedColumn1 is inserted with the values calculated by the formula you entered.

calculatedcolumn1

  1. Double click the header of the new calculated column.

  2. Rename the header as TotalProductPrice.

totalproductprice

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

Add one more calculated column for Gross Profit as follows −

  1. Click the Design tab on the Ribbon.

  2. Click Add.

  3. The column on the right side with the header − Add Column is highlighted.

  4. Type = [TotalSalesAmount] − [TotaProductPrice] in the formula bar.

  5. Press Enter.

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

A new column with header CalculatedColumn1 is inserted with the values calculated by the formula you entered.

new column

  1. Double click the header of the new calculated column.

  2. Rename the header as Gross Profit.

header as gross profit

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

Add the Calculated Columns in the North_Sales data table in a similar way. Consolidating all the steps, proceed as follows −

  1. Click the Design tab on the Ribbon.

  2. Click Add. The column on the right side with the header − Add Column is highlighted.

  3. Type = [Product Price] * [No. of Units] in the formula bar and press Enter.

  4. A new column with header CalculatedColumn1 gets inserted with the values calculated by the formula you entered.

  5. Double click the header of the new calculated column.

  6. Rename the header as TotalProductPrice.

  7. Click the Design tab on the Ribbon.

  8. Click Add. The column on the right side with the header - Add Column is highlighted.

  9. Type = [TotalSalesAmount] − [TotaProductPrice] in the formula bar and press Enter. A new column with header CalculatedColumn1 gets inserted with the values calculated by the formula you entered.

  10. Double click the header of the new calculated column.

  11. Rename the header as Gross Profit.

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

Repeat the above given steps for the South Sales data table and West Sales data table.

north sales

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

You have the necessary columns to summarize the Gross Profit. Now, create the Power PivotTable.

summarize gross profit

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

You are able to summarize the Gross Profit that became possible with the calculated columns in the Power Pivot and it all can be done just in a few steps that are error-free.

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

You can summarize it region wise for the products as given below also −

profit

Calculated Field

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

Suppose you want to calculate the percentage of profit made by each region product-wise. You can do so by adding a calculated field to the Data Table.

  1. Click below the column Gross Profit in the East_Sales table in Power Pivot window.

  2. Type EastProfit: = SUM ([Gross Profit]) / sum ([TotalSalesAmount]) in the formula bar.

  3. Press Enter.

eastprofit

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

The calculated field EastProfit is inserted below the Gross Profit column.

  1. Right click the calculated field − EastProfit.

  2. Select Format from the dropdown list.

select format

格式设置对话框会出现。

The Formatting dialog box appears.

  1. Select Number under Category.

  2. In the Format box, select Percentage and click OK.

select number

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

The calculated field EastProfit is formatted to percentage.

percentage

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

Repeat the steps to insert the following calculated fields −

  1. NorthProfit in North_Sales data table.

  2. SouthProfit in South_Sales data table.

  3. WestProfit in West_Sales data table.

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

Note − You cannot define more than one calculated field with a given name.

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

Click on the Power PivotTable. You can see that the calculated fields appear in the tables.

repeat steps

  1. Select the fields − EastProfit, NorthProfit, SouthProfit and WestProfit from the tables in the PivotTable Fields list.

  2. Arrange the fields such that the Gross Profit and Percentage Profit appear together. The Power PivotTable looks as follows −

percentage profit

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

Note − The Calculate Fields were called Measures in earlier versions of Excel.