教程:在 Power BI Desktop 中创建你自己的度量值Tutorial: Create your own measures in Power BI Desktop

通过使用度量值,可以在 Power BI Desktop 中创建某些功能强大的数据分析解决方案。Some of the most powerful data analysis solutions in Power BI Desktop can be created by using measures. 度量值可在我们与报表进行交互时帮助我们对数据执行计算。Measures help us by performing calculations on our data as we interact with our reports. 本教程将指导你了解并在 Power BI Desktop 中创建一些你自己的基本度量值。This tutorial will guide you through understanding and creating some of your own basic measures in Power BI Desktop.

本文面向已熟悉使用 Power BI Desktop 创建更高级的模型的 Power BI 用户。This article is intended for Power BI users already familiar with using Power BI Desktop to create more advanced models. 你应该已经熟悉使用“获取数据”和“查询编辑器”来导出数据、使用多个相关表和向报表画布添加字段。You should already be familiar with using Get Data and Query Editor to import data, working with multiple related tables, and adding fields to the Report Canvas. 如果刚开始使用 Power BI Desktop,请务必查看 Power BI Desktop 入门If you’re new to Power BI Desktop, be sure to check out Getting Started with Power BI Desktop.

若要完成本教程中的步骤,你需要下载 Power BI Desktop 的 Contoso 销售示例文件。To complete the steps in this tutorial, you’ll need to download the Contoso Sales Sample for Power BI Desktop file. 它已包含来自虚构公司 Contoso,inc. 的线上销售数据。因为文件中的数据是从数据库导入的,你将无法连接到数据源或在查询编辑器中查看。It already includes online sales data from the fictitious company, Contoso, Inc. Because data in the file was imported from a database, you won’t be able to connect to the datasource or view it in Query Editor. 当你自己的计算机上有此文件时,请直接在 Power BI Desktop 中将它打开。When you have the file on your own computer, go ahead and open it in Power BI Desktop.

这些度量值有什么用?What are these measures all about?

度量值往往是自动创建的,如当我们从字段列表中的 Sales 表选择 SalesAmount 字段旁边的复选框或将 SalesAmount 拖动到报表画布时(便会自动创建)。Measures are most often created for us automatically, like when we select the checkbox next to the SalesAmount field from the Sales table in the field list, or drag SalesAmount onto the Report canvas.

将会显示如下的新图表可视化效果:A new chart visualization appears, like this:

我们得到一个在 SalesAmount 字段显示销售总额的柱形图。What we get is a Column chart showing a sum total amount of sales values from the SalesAmount field. SalesAmount 字段实际上只是一个名为 SalesAmount 的列,它位于我们已导入的 Sales 表中。Our SalesAmount field is really just a column named SalesAmount in the Sales table we already imported.

SalesAmount 列包含超过两百万行的销售值。The SalesAmount column contains over two million of rows of sales values. 你可能想知道为什么看不到具有所有这些值的行的表。You might be wondering why you don’t see a table with rows of all those values. Power BI Desktop 知道 SalesAmount 中的所有值都属于数字数据类型,并且你可能想要以某种方式将它们聚合,可能是求和、求平均值和计数等...Well, Power BI Desktop knows that all of the values in SalesAmount are of a numeric datatype, and you’ll probably want to aggregate them in some way, whether it be adding them up, averaging, counting, etc..

只要你在字段列表中看到拥有 sigma 图标 的字段,就表示此字段是数值并且它的值可以聚合。Whenever you see a field in the Fields list with a sigma icon , it means the field is numeric, and its values can be aggregated. 在这种情况下,当我们选择 SalesAmount 时,Power BI Desktop 将创建其自己的度量值,并且将在图表中显示计算出的所有销售额的总数。In this case, when we select SalesAmount, Power BI Desktop creates its own measure and the sum of all sales amounts is calculated and displayed in our chart.

当我们选择具有数字数据类型的字段时,总和将是默认的聚合方式,但是我们也可以轻易地将其更改为不同的聚合类型。Sum is the default aggregation when we select a field with a numeric datatype, but we can change to a different type of aggregation quite easily.

Value 区域中,如果我们单击 SalesAmount 旁边的向下箭头,就可以选择 AverageIn the Value area, if we click the down arrow next to SalesAmount, we can then select Average.

SalesAmount 字段中的可视化效果将更改为所有销售值的平均值。Our visualization changes to an average of all sales values in the SalesAmount field.

我们可以根据想要的结果来更改聚合的类型,但并非所有类型的聚合都能应用于任何数字数据类型。We can change the type of aggregation depending on the result we want, but not all types of aggregation apply to just any numeric datatype. 例如,对于 SalesAmount 字段,总和和平均值是有意义的。For example, for our SalesAmount field, Sum and Average make sense. 最小值和最大值也有它们的意义。Minimum and Maximum have their place as well. 但是,计数对于 SalesAmount 字段则没有太大意义,因为虽然它的值是数值,但它们实际上是货币。But, Count won’t really make much sense for our SalesAmount field because while its values are numeric, they’re really currency.

理解聚合是了解度量值的基础,因为每个度量值都将执行某种类型的聚合。Understanding aggregations is fundamental to understanding measures, because every measure will perform some type of aggregation. 稍后在你创建自己的一些度量值时,我们将看到使用总和聚合的更多示例。We’ll see more examples of using a Sum aggregation a little later, when you create some of your own measures.

度量值计算的值会随时根据我们与报表的交互而发生变化。Values calculated from measures are always changing in response to our interactions with our report. 例如,如果我们将 RegionCountryName 字段从 Geography 表拖动到我们的表格,则会计算每个国家/地区的销售额的平均值并显示出来。For example, if we drag the RegionCountryName field from the Geography table to our chart, sales amounts for each country are averaged and displayed.

由于与报表进行交互而导致度量值更改时,也会影响我们度量值的 上下文When the result of a measure changes because of an interaction with our report, we are affecting our measure’s context. 事实上,每当你与报表交互时,都会改变上下文中度量值的计算和其显示的结果。In fact, every time you interact with your report, you are changing the context in which a measure calculates and displays its results.

大多数情况下,Power BI 会执行操作、计算并根据我们已添加的字段和我们选择的聚合类型返回值。In most cases, Power BI does its thing and calculates and returns values according to the fields we’ve added and the types of aggregation we choose. 但在其他情况下,你可能需要创建你自己的度量值才能执行更复杂的和独特的计算。But in other cases, you might have to create your own measures to perform more complex, unique calculations.

使用 Power BI Desktop,你可以创建你自己的具有数据分析表达式 (DAX) 公式语言的度量值。With Power BI Desktop, you create your own measures with the Data Analysis Expressions (DAX) formula language. DAX 公式非常类似于 Excel 公式。DAX formulas are very similar to Excel formulas. 实际上,DAX 使用许多与 Excel 公式相同的函数、运算符和语法。In fact, DAX uses many of the same functions, operators, and syntax as Excel formulas. 但是,DAX 的函数用于在我们与报表进行交互时处理关系数据,并执行更动态的计算。However, DAX’s functions are designed to work with relational data and perform more dynamic calculations as we interact with our reports.

超过 200 个 DAX 函数可以执行任何计算,从总和和平均值的简单聚合到更复杂的统计和筛选函数。There are over 200 DAX functions that do everything from simple aggregations like Sum and Average to more complex statistical and filtering functions. 我们不打算在此处详细探讨 DAX 语言,但仍有许多资源可帮助你了解详细信息。We’re not going to go into too much detail on the DAX language here, but there are many resources to help you learn more. 完成本教程后,请务必参阅 Power BI Desktop 中的 DAX 基础知识After you've gone through this tutorial, be sure to see DAX basics in Power BI Desktop.

当我们创建我们自己的度量值时,它们会添加到我们所需要的表的字段列表中。When we create our own measures, they’re added to the Fields list for the table we want. 我们将此称为 模型 度量值,它将作为字段保留在报表中。This is known as a model measure, and it will remain in our table as a field. 模型度量值的几大优势在于,我们可以任意命名模型度量值,使其更容易识别。Some of the great advantages of model measures are that we can name them what we want, making them more identifiable. 我们还可以将它们用作其他 DAX 表达式中的参数,并且可以创建能快速地执行复杂计算的度量值。We can also use them as an argument in other DAX expressions, and we can create measures that perform complex calculations very quickly.

我们来创建自己的度量值Let’s create our own measure

假设我们要分析净销售额。Let’s say we want to analyze our net sales. 如果查看字段列表中的 Sales 表,我们将看到并没有名为 NetSales 的字段。If we look at our Sales table in the field list, we see that there's no field named NetSales. 但是,我们有构建基块,可以用来创建我们自己的度量值以计算净销售额。But, we have the building blocks to create our own measure to calculate net sales.

我们需要从销售额中减去折扣并返回值的度量值。We need a measure to subtract discounts and returns from sales amounts. 因为我们希望度量值能对可视化效果中的任何上下文进行计算,事实上,我们需要从 SalesAmount 的总和中减去 DiscountAmount 和 ReturnAmount 的总和。Because we want our measure to calculate a result for whatever context we have in our visualization, in-effect, we need to subtract the sum of DiscountAmount and ReturnAmount from sum of SalesAmount. 现在看起来可能有点令人困惑;别担心,稍后就会更加清楚。This might seem a little confusing at the moment; don’t worry, it will be more clear in a little bit.

净销售额Net sales

  1. 右键单击或单击字段列表中的 Sales 表上的向下箭头,然后单击新建度量值Right click, or click the down arrow on the Sales table in the field list, and then click New Measure. 这将确保我们的新建度量值保存在 Sales 表中,以使其更易于查找。This will make sure our new measure is saved in the Sales table, where it will be easier to find.


    还可以通过单击 Power BI Desktop 开始选项卡上的功能区中的“新建度量值”按钮来创建新的度量值。You can also create a new measure by clicking on the New Measure button in the ribbon on Power BI Desktop’s Home tab.

    从功能区中创建度量值时,可以在其中任何一个表中创建度量值。When you create a measure from the ribbon, the measure could be created in any of the tables. 虽然度量值不属于任何特定的表,但如果在对你来说最具逻辑关系的表中创建,可能会更容易找到。While a measure doesn’t have to belong in a particular table, it will be easier to find if you create them in a table most logical to you. 如果你希望它位于特定的表中,首先请单击表,以使处于活动状态。If you want it to be in a particular table, click the table first, to make it active. 然后单击“新建度量值”。Then click New Measure. 在本例中,我们将在 Sales 表中创建我们的第一个度量值。In our case, we’re going to create our first measure in the Sales table.

    编辑栏将出现在报表画布的顶部。The formula bar appears along the top of the Report Canvas. 我们可以在此处重命名度量值并输入 DAX 公式。This is where we can rename our measure and enter a DAX formula.

    让我们为新度量值指定名称。Let’s give our new measure a name. 默认情况下,新度量值的名称就是“度量值”。By default a new measure is simply named Measure. 如果我们不进行重命名,当我们创建其他度量值时,便会命名为度量值 2、度量值 3,依此类推。If we don’t rename it, when we create another, it will be named Measure 2, Measure 3, and so on. 我们希望我们的度量值更易于识别,因此我们将新度量值命名为 Net Sales。We want our measures to be more identifiable, so let’s name our new measure Net Sales.

  2. 在编辑栏内,选中“Measure”,再键入“Net Sales”。Highlight Measure in the formula bar, and then type Net Sales.

    现在我们可以开始输入公式。Now we can begin entering our formula.

  3. 在等号后键入 S。你将看到显示出来一个下拉建议列表,其中有以字母 S 开头的所有的 DAX 函数。我们键入的字母越多,建议列表就更能调整至接近我们所需的函数。After the equals sign type an S. You’ll see a dropdown suggestion list appear with all of the DAX functions beginning with the letter S. The more we type, the more the suggestion list is scaled closer to the function we need. 通过向下滚动选择 SUM,然后按 Enter。Select SUM by scrolling down, and then press Enter.

    按 Enter 后,将会出现一个左括号和另一个建议清单,其中包括我们可以传递到 SUM 函数的所有可用的列。After we press Enter, an opening parenthesis appears along with another suggestion list of all of the available columns we can pass to the SUM function.

    表达式将始终出现在左括号和右括号之间。An expression always appears between an opening and closing parenthesis. 在这种情况下,我们的表达式将包含传递到 SUM 函数的单个参数;也就是要计算总和的列。In this case, our expression is going to contain a single argument to pass to the SUM function; a column to sum up. 通过键入我们想要的第一个字母,我们可以缩小列的列表。We can narrow down our list of columns by typing the first letters of what we want. 在这种情况下,我们需要 SalesAmount 列,以便当我们开始键入 salesam 时,列表范围会变小并显示我们可以选择的两个项。In this case, we want the SalesAmount column, so when we begin typing salesam, our list gets smaller, and we are shown two items we can select. 它们实际上是相同的列。They’re actually the same column. 其中一列只显示 [SalesAmount],因为我们在 SalesAmount 列所在的相同表中创建度量值。One just shows [SalesAmount], because we’re creating our measure in the same table the SalesAmount column is in. 而另一列,我们将在列名称前看到表名称。The other, we see the table name preceding the column name.

    一般情况下,最好输入列的完全限定名称。In general, it’s good practice to enter the fully qualified name of a column. 这将使你的公式更易于阅读。It will make your formulas easier to read.

  4. 选择“Sales[SalesAmount]”,再键入右括号。Select Sales[SalesAmount], and then type a closing parenthesis.


    语法错误通常由缺少或错放右括号导致。Syntax errors are most often caused by a missing or misplaced closing parenthesis.

    现在,我们想要减去其他两列。Now we want to subtract our other two columns.

  5. 在第一个表达式的右括号后依次键入一个空格、减号运算符 (-) 和另一个空格。After the closing parenthesis for our first expression, type a space, and then a minus operator (-), followed by another space. 然后输入另一个将 Sales [DiscountAmount] 列用作参数的 SUM 函数。Then enter another SUM function with the Sales[DiscountAmount] column as its argument.

    我们已逐渐将公式的空间用完了。We’re starting to run out of space for our formula. 没问题。No problem.

  6. 单击编辑栏右侧的向下 v 形图标。Click the down chevron on the right side of the formula bar.

    现在我们有了更多的空间。Now we have more space. 我们可以按 Alt-Enter 在新行上向我们的公式输入新的部分。We can enter new parts to our formula on a new line by pressing Alt-Enter. 我们还可以使用选项卡移动内容。We can also move things over by using Tab.

    现在我们可以添加公式的最后一部分。Now we can add the final part of our formula.

  7. 添加另一个减号运算符,并在后面添加另一个 SUM 函数和 Sales [ReturnAmount] 列作为其参数。Add another minus operator followed by another SUM function and the Sales[ReturnAmount] column as its argument.

    现在,我们的公式已经准备就绪。Our formula now looks ready.

  8. 按 Enter 或单击编辑栏中的选中标记完成操作。Press Enter or click the checkmark in the formula bar to complete. 该公式将经过验证并添加到 Sales 表中的字段列表。The formula is validated and added to the field list in the Sales table.

让我们将新度量值添加到报表Let’s add our new measure to a report

现在我们可以将 Net Sales 度量值添加到报表画布,之后将对我们添加到报表中的任何字段计算净销售额。Now we can add our Net Sales measure to the report canvas, and net sales will be calculated for whatever other fields we add to the report. 让我们按国家/地区来查看净销售额。Let’s look at net sales by country.

  1. Net Sales 度量值从 Sales 表拖动到报表画布。Drag the Net Sales measure from the Sales table onto the Report canvas.

  2. 现在,将“RegionCountryName”字段从“Geography”表拖到图表中。Now drag the RegionCountryName field from the Geography table into the chart.

    让我们再添加一些数据。Let’s add some more data.

  3. SalesAmount 字段拖动到图表,以查看净销售额和销售额之间的差异。Drag the SalesAmount field into the chart, to see the difference between net sales and sales amount.

    我们现在在图表中实际上有两个度量值。We now really have two measures in our chart. 自动计算总和的 SalesAmount 和我们创建的 Net Sales 度量值。SalesAmount, which was summed up automatically, and the Net Sales measure we created. 在每个案例中,这些结果会依据图表中另一字段的上下文计算,即 RegionCountryName 中的国家/地区。In each case, the results were calculated in context of another field we have in the chart, the countries in RegionCountryName.

    让我们添加一个切片器,以便我们可以按日历年份进一步细分净销售额和销售总额。Let’s add a Slicer, so we can further break down our net sales and sales amounts by calendar year.

  4. 单击图表旁的空白区域,然后在可视化效果中,单击表可视化效果。Click a blank area next to the chart, then in Visualizations, click on the Table visualization.

    这会在报表画布中创建一个空白的表可视化效果。This creates a blank table visualization in the Report canvas.

  5. Year 字段从 Calendar 表拖动到新的空白表。Drag the Year field from the Calendar table into the new blank table.

    因为 Year 是数值字段,Power BI Desktop 将计算其值的总和并为我们提供一个图表。Because Year is a numeric field, Power BI Desktop summed up its values and gave us a chart. 但是,与切片器相比,这样做作用不大。But, that doesn’t do us much good as a Slicer.

  6. 在“值”中,依次单击“年份”旁边的向下箭头和“不汇总”。In Values, click the down arrow next to Year, and then click Do Not Summarize.

    现在,我们可以将表可视化效果中的 Year 字段更改到切片器中。Now we can change the Year field in the table visualization into a Slicer.

    1. 可视化效果中单击切片器可视化效果。In Visualizations, click the Slicer visualization.

      现在我们将 Year 字段作为切片器。Now we have Year as a Slicer. 我们可以选择任何单独或群组年份,报表的可视化效果将据此进行切分。We can select any individual or group of years and our report’s visualizations will all be sliced accordingly.

  7. 继续操作并单击“2013 年”。Go ahead and click on 2013. 你将看到图表发生更改。You’ll see the chart change. 我们的 Net Sales 和 SalesAmount 度量值将进行重新计算,并仅显示 2013 年的新结果。Our Net Sales and SalesAmount measures are re-calculated, showing new results just for 2013. 再次重申,我们已更改了度量值在其中计算和显示结果的上下文。Here again, we’ve changed the context in which our measures calculate and display results.

让我们创建另一个度量值Let’s create another measure

现在你已经知道了如何创建你自己的度量值,让我们再创建一个。Now that you know how to create your own measures, let’s create a another.

每单位净销售额Net sales per unit

如果我们想要了解哪些产品每单位销售的销售额最高要怎么办呢?What if we want to find out which products with the most sales per unit sold is?

我们可以创建另一个度量值。Well, we can create another measure. 在本案例中,我们想要用净销售额除以单位销售量。In this case, we want to divide net sales by the quantity of units sold. 事实上,我们要用 Net Sales 度量值的结果除以 Sales[SalesQuantity] 的总和。In-effect, we want to divide the result of our Net Sales measure by the sum of Sales[SalesQuantity].

  1. 在 Sales 或 Products 表中创建名为 Net Sales per Unit 的新度量值。Create a new measure named Net Sales per Unit in either the Sales or Products table.

    在此度量值中,我们将使用我们之前创建的 Net Sales 度量值。In this measure, we’re going to use the Net Sales measure we created earlier. 使用 DAX,我们可以在公式中参考其他度量值。With DAX, we can reference other measures in our formula.

  2. 开始键入 Net SalesBegin typing Net Sales. 建议列表将显示我们可以添加的内容。The suggestion list will show what we can add. 选择 [Net Sales]Select [Net Sales].

    此外还可以参考其他度量值,通过键入一个左方括号 ([) 即可。You can also reference another measure by just typing an opening bracket ([). 建议列表将仅向我们显示能添加到公式的度量值。The suggestion list will show us only the measures we can add to our formula.

  3. [Net Sales] 后依次输入一个空格、一个除号运算符 (/) 和一个 SUM 函数,然后键入 QuantityRight after [Net Sales], enter a space, then a divide operator (/), then enter a SUM function, then type Quantity. 建议列表将显示名称中具有 Quantity 的所有列。The suggestion list shows all of the columns with Quantity in the name. 选择 Sales [SalesQuantity]Select Sales[SalesQuantity]. 该公式现在应如下所示:The formula should now look like this:

    Net Sales per Unit = [Net Sales] / SUM(Sales[SalesQuantity])Net Sales per Unit = [Net Sales] / SUM(Sales[SalesQuantity])

    很酷吧?Pretty cool, huh? 当我们使用 DAX 编辑器的搜索和建议功能时,输入 DAX 公式其实特别简单。Entering DAX formulas is really quite easy when we use the DAX Editor’s search and suggestion functionality. 现在,让我们看看新的 Net Sales per Unit 度量值中有什么。Now, let’s see what we get with our new Net Sales per Unit measure.

  4. 将“每单位净销售额”度量值拖到报表画布中的空白区域。Drag the Net Sales per Unit measure onto a blank area in the report canvas.

    不怎么有趣是吗?Not very interesting is it? 别担心。Don’t worry.

  5. 将图表可视化效果类型更改为树状图Change the chart visualization type to Tree Map.

  6. 现在,将“ProductCategory”字段从“ProductCategory”表向下拖到“组”区域中。Now drag the ProductCategory field from the ProductCategory table down into the Group area.

    这些信息很有用,但如果我们想要按产品查看净销售额呢?That’s some good info, but what if we want to look at net sales by product?

  7. 删除“ProductCategory”字段,再改为将“ProductName”字段从“Product”表向下拖到“组”区域中。Remove the ProductCategory field, and then drag the ProductName field from the Product table down into the Group area instead.

    好了,我们只是热身而已,但你不得不承认,真的很酷!Ok, now we're just playing, but you have to admit, that's just cool! 当然,我们可以用多种方法筛选此树状图,但这超出了本教程的范围。Of course, we can filter this tree map down any number of ways, but that's out of scope for this tutorial.

所学内容What we’ve learned

度量值为我们深入了解数据提供了强大的功能。Measures give us a lot of power in getting the insights we want from our data. 我们已经学习了如何使用公式栏创建度量值。We’ve learned how to create measures by using the formula bar. 我们可以为度量值命名最有意义的名称,并且建议列表使查找并选择正确元素以将它们添加到公式变得更简单。We can name measures whatever makes most sense, and the suggestion lists make it easy to find and select the right element to add to our formulas. 我们还介绍了上下文,其中度量值中的计算结果会根据其他字段或度量值公式中的其他表达式而发生更改。We’ve also been introduced to context, where the result of calculations in measures change according to other fields, or by other expressions in your measure formula.

后续步骤Next steps

如果你想要深入了解 DAX 公式和创建更高级的度量值,请参阅 Power BI Desktop 中的 DAX 基础知识If you want to take a deeper dive into DAX formulas, and create some more advanced measures, see DAX basics in Power BI Desktop. 本文重点在于介绍 DAX 中的基本概念,如语法、函数和对上下文的透彻理解。This article focuses on fundamental concepts in DAX, such as syntax, functions, and a more thorough understanding of context.

请务必将数据分析表达式 (DAX) 参考添加到收藏夹。Be sure to add the Data Analysis Expressions (DAX) Reference to your favorites. 你可以在这里找到有关 DAX 语法、运算符和 200 多个 DAX 函数的详细信息。This is where you'll find detailed info on DAX syntax, operators, and the over 200 DAX functions.