教學課程:在 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 DesktopIf 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] 欄位,其實只是在我們已匯入的 [Sales] 資料表中名為 [SalesAmount] 的資料行。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.

在 [值] 區域中,如果我們按一下 [SalesAmount] 旁邊的向下箭號,我們即可選取 [平均] 。In 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. 例如,如果我們從 [Geography] 資料表將 [RegionCountryName] 欄位拖曳至圖表,就會平均並顯示每個國家/地區的銷售金額。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. 在公式列中反白顯示 量值,然後鍵入 Net SalesHighlight 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. 然後輸入另一個的 SUM 函數,其中引數為 Sales [DiscountAmount] 資料行。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 UnitCreate 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 函數,然後輸入數量Right after [Net Sales], enter a space, then a divide operator (/), then enter a SUM function, then type 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 有什麼。Now, let’s see what we get with our new Net Sales per Unit measure.

  4. 將 [Net Sales per Unit] 量值拖曳至報表畫布中的空白區域。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] 資料表向下拖曳到 [Group] 區域。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] 資料表向下拖曳到 [Group] 區域。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.