Excel Dax 简明教程

Excel DAX - Calculated Fields / Measures

数据模型中的表中的 calculated field 是通过 DAX 公式获得的字段。在早期版本的 Power Pivot 中，计算字段被称为度量。在 Excel 2013 中，它被重新命名为计算字段。但是，在 Excel 2016 中，它又被重新命名为度量。如果您参考任何文档，您会观察到这两个术语混淆在一起。请注意，计算字段和度量这两个术语是同义的。在本教程中，我们使用术语计算字段。

A calculated field in a table in a Data Model is the field obtained by a DAX formula. In earlier versions of Power Pivot, the calculated field was termed as a measure. In Excel 2013, it was renamed as a calculated field. However, it is renamed back to measure in Excel 2016. If you refer to any documentation, you can observe a mix up of these two terms. Note that the terms calculated field and measure are synonymous. In this tutorial, we use the term calculated field.

Understanding Calculated Fields

计算字段是专门为在数据透视表（或数据透视图）中使用而创建的公式。

A calculated field is a formula that is created specifically for use in a PivotTable (or PivotChart).

您可以基于标准聚合函数（如 COUNT 或 SUM）或通过定义自己的 DAX 公式来创建计算字段。

You can create a calculated field based on standard aggregation functions, such as COUNT or SUM, or by defining your own DAX formula.

以下是计算字段和计算列之间的区别 −

Following is the difference between the calculated field and the calculated column −

  1. A calculated field can be used only in the VALUES area of a PivotTable.

  2. A calculated column with the calculated results can be used in ROWS, COLUMNS and FILTERS areas also.

Saving Calculated Field

计算字段将与它的源表一起保存在数据模型中。它作为表中的一个字段显示在 Power PivotTable 或 Power PivotChart 字段列表中。

The calculated field will be saved with its source table in the Data Model. It appears in the Power PivotTable or Power PivotChart Fields list as a field in the table.

Using Calculated Field

要使用计算字段，您必须从 Power PivotTable 字段列表中选择它。计算字段将添加到 VALUES 区域，并且将评估用于计算字段的公式。为行列字段的每个组合创建一个结果。

To use a calculated field, you have to select it from the Power PivotTable Fields list. The calculated field will get added to the VALUES area and the formula used for the calculated field will be evaluated. A result is created for each combination of row and column fields.

Calculated Field – An Example

考虑以下奥运会数据的数据模型 −

Consider the following Data Model for Olympics data −

calculated field

如以上屏幕截图所示，Results 表有一个 Medal 字段，其中包含值 – Gold、Silver 或 Bronze，用于每个包含 Sport – Event - Country – Date 组合的行。假设您想要每个国家的奖牌数，那么您可以使用以下 DAX 公式创建一个计算字段 Medal Count −

As seen in the above screenshot, the Results table has a field Medal that contains the values – Gold, Silver, or Bronze for each of the rows containing Sport – Event - Country – Date combination. Suppose you want medal count for each country, then you can create a calculated field Medal Count with the following DAX formula −

Medal Count := COUNTA([Medal])

Creating a Calculated Field in a Table

若要在 Results 表中创建计算字段 Medal Count，请执行以下操作 −

To create the calculated field Medal Count in the Results table, do the following −

  1. Click the cell in the calculation area below the Medal column in the Results table. The cell will be highlighted.

  2. Type Medal Count:=COUNTA([Medal]) in the formula bar.

creating calculated field in table

按 Enter 键。

Press Enter.

press enter

如以上屏幕截图所示，计算字段显示在所选单元格中，显示值为 34,094。这个数字是 Results 表中的总行数。因此，乍一看并没有多大意义。正如前面所讨论的，只有将计算字段添加到 Power PivotTable 或 Power PivotChart 中才能看到它的实际用途。

As seen in the above screenshot, the calculated field appears in the selected cell, showing the value as 34,094. This number is the total number of rows in the Results table. Hence, it does not make much sense at the first look. As discussed earlier, the real use of a calculated field can be seen only by adding it to a Power PivotTable or a Power PivotChart.

Using the Calculated Field in a Power PivotTable

若要使用计算字段计算每个国家的奖牌数，请执行以下操作 −

To use the calculated field to count the number of medals for each country, do the following −

  1. Click the PivotTable on the Ribbon in the Power Pivot window.

  2. Click the PivotTable in the dropdown list.

using calculated field in power pivottable

将出现创建数据透视表对话框。

Create PivotTable dialog box appears.

  1. Click the Existing Worksheet.

  2. Select where you want to place the PivotTable.

将创建一个空数据透视表。

An empty PivotTable will get created.

  1. Click the Results table in the PivotTable Fields list.

  2. Click the fields – Country and Medal count.

empty pivottable created

如您所见，奖牌数被添加到值区域，国家/地区被添加到行区域。使用国家/地区字段值出现在行中的数据透视表进行创建。并且针对每一行，对奖牌数进行计算和显示。正是以此方式，计算出的字段评估所用的 DAX 公式并显示值。

As you can observe, Medal Count is added to VALUES area and Country is added to ROWS area. The PivotTable is created with the field Country values appearing in the rows. And for each row, the Medal Count value is calculated and displayed. That is the way, the calculated field evaluates the DAX formula used and displays the values.

  1. Add the field Sport from the Results table to ROWS area.

add field sport

正如您在上述屏幕截图中看到的那样，对每个国家/地区计算奖牌数，由运动分类，以及针对国家/地区本身计算一个子总数。

As you can see in the above screenshot, Medal Count is calculated for each Country - Sport-wise and a Subtotal for the Country itself.

这是 DAX 补充 Power 功能的方式。

This is how DAX supplements the Power features.

Types of Calculated Fields

计算字段有两种类型 - 隐式和显式。

There are two types of Calculated Fields – Implicit and Explicit.

  1. An implicit calculated field is created in the Power PivotTable Fields list pane.

  2. An explicit calculated field is created either in the table in the Power Pivot window, or from the PowerPivot Ribbon in the Excel window.

Creating an Implicit Calculated Field

隐式计算字段可以用两种方式创建，都位于 PowerPivot 数据透视表字段窗格中。

An implicit calculated field can be created in two ways, both in the Power PivotTable Fields pane.

Creating an Implicit Calculated Field in the PivotTable Fields List

您可以使用数据透视表字段列表中的奖牌字段创建奖牌数量字段，如下所示 -

You can create the Count of Medal Field from the Medal field in the PivotTable Fields list as follows −

  1. Deselect the field Medal Count.

  2. Right-click on the field Medal.

  3. Click Add to Values in the dropdown list.

creating implicit calculated field

奖牌数出现在值区域中。奖牌数列将添加到数据透视表。

Count of Medal appears in the Values area. Count of Medal column will be added to the PivotTable.

medal column count

Creating an Implicit Calculated Field in the VALUES Area

您可以在值区域中创建隐式计算字段 - 父行百分比，以表达一个国家/地区获得的每项运动的奖牌数占该国家/地区获得的奖牌总数的百分比。

You can create an implicit calculated field - % of Parent Row in the Values area to express the Medal count of each sport that a country has won as a percentage of the total number of Medals won by that Country.

  1. Click the down arrow in the Count of Medal box in VALUES area.

  2. Click the Value Field Settings in the dropdown list.

creating implicit calculated field in values area

将出现Value Field Settings对话框。

Value Field Settings dialog box appears.

  1. Type % Medals in the Custom Name box.

  2. Click the Show Values As tab.

  3. Click the box under Show values as.

  4. Click the % of Parent Row Total.

value field settings

  1. Click the Number Format button.

将出现“设置单元格格式”对话框。

Format Cells dialog box appears.

  1. Click Percentage.

  2. Type 0 in decimal places.

  3. Click OK.

  4. Click OK in the Value Field Settings dialog box.

  5. Select Do Not Show Subtotals.

format cells dialog box

你创建了另一个隐式计算域“% Medals”，正如你所观察到的，对于每个国家，每一项体育竞技所占的奖牌比例都会显示出来。

You created another implicit calculated field % Medals and as you can observe, for each Country, the percentage of Medals Sport-wise are displayed.

Drawbacks of an Implicit Calculated Field

隐式计算域容易创建。事实上，你一直在 Excel 数据透视表和透视图表中创建它们。但是，它们有以下缺点：

Implicit calculated fields are easy to create. In fact, you have been creating them even in Excel PivotTables and Pivot Charts. But, they have the following drawbacks −

  1. They are volatile. That means, if you deselect the field you used for calculated field, it will be removed. If you want to display it again, you have to once again create it.

  2. Their scope is limited to the PivotTable or PivotChart in which they are created. If you create another PivotTable in another worksheet, you have to create the calculated field again.

另一方面，显式计算域将与数据表一起保存，并且在你选择该数据表时可用。

On the other hand, explicit calculated fields will get saved with the table and will be available whenever you select that table.

Creating an Explicit Calculated Field

你可以用两种方式创建显式计算域：

You can create an explicit calculated field in two ways −

  1. In the calculation area in a Table in the Data Model. You have already learnt this in the section – Creating Calculated Field in a Table.

  2. From PowerPivot Ribbon in the Excel table. You will learn this way of creating an explicit calculated field in the next section.

Creating an Explicit Calculated Field from PowerPivot Ribbon

要从 PowerPivot 功能区创建显式计算域，请执行以下步骤：

To create an explicit calculated field from PowerPivot Ribbon, do the following −

  1. Click the POWERPIVOT tab on the Ribbon in your workbook.

  2. Click the Calculated Fields in the Calculations area.

  3. Click the New Calculated Field in the dropdown list.

creating explicit calculated

计算字段对话框出现。

Calculated Field dialog box appears.

  1. Fill in the required information as shown in the following screenshot.

calculated field dialog box

  1. Click the Check formula button.

  2. Click OK only if there are no errors in the formula.

如您所见，您可以在此对话框中定义计算字段的类别和格式。此外，您可以使用 IntelliSense 功能了解函数的使用，并使用自动完成功能轻松完成函数、表和列的名称。有关 IntelliSense 功能的详细信息，请参阅章节 – DAX Formulas 。

As you can observe, you can define the category and format of the calculated field in this dialog box. Further, you can use the IntelliSense feature to understand the usage of the functions and to use the AutoComplete feature to easily complete the names of the functions, tables, and columns. For details on IntelliSense feature, refer to the chapter – DAX Formulas.

这是创建显式计算字段的推荐方式。

This is a recommended way to create explicit calculated fields.