Power BI 視覺效果中的彙總Aggregates in Power BI visualizations

什麼是彙總?What is an aggregate?

有時候您會想要以數學方式結合資料中的值。Sometimes you want to mathematically combine values in your data. 數學運算可能是加總、平均、最大值、計數等等。當您結合資料中的值時,它稱為「彙總」。The mathematical operation could be sum, average, maximum, count, etc. When you combine values in your data, it is called aggregating. 數學運算的結果是「彙總值」。The result of that mathematical operation is an aggregate.

Power BI 服務和 Power BI Desktop 建立視覺效果時,可能會彙總資料。When Power BI service and Power BI Desktop create visualizations, they may aggregate your data. 通常彙總就是所需要的結果,但有時候您可能想要以不同方式來彙總值。Often the aggregate is just what you need, but other times you may want to aggregate the values in a different way. 例如,總和與平均值。For example, a sum versus an average. 有幾種不同方式來管理和變更視覺效果正在使用的彙總。There are several different ways to manage and change the aggregate being used in a visualization.

首先,讓我們查看資料「類型」,因為資料類型會決定如何彙總它,以及是否可以彙總。First, let's take a look at data types because the type of data determines how, and if, it can be aggregated.

資料類型Types of data

大部分的資料集有多個資料類型。Most datasets have more than one type of data. 在最基本的層級,資料會是數值或不是數值。At the most basic level, the data is either numeric or it is not. 數值資料可以使用總和、平均值、計數、最小值、變異數,以及更多方式來彙總。Numeric data can be aggregated using a sum, average, count, minimum, variance, and much more. 即使是文字資料,通常稱為「類別目錄」資料,也可以彙總。Even textual data, often called categorical data, can be aggregated. 如果您嘗試彙總類別欄位 (藉由將它放在僅限數值的值區,例如工具提示,Power BI 將計算每個類別的出現次數,或計算每個類別的相異出現次數。If you try to aggregate a categorical fields (by placing it in a numeric only bucket like Values or Tooltips), Power BI will count the occurrences of each category or count the distinct occurrences of each category. 特殊類型的資料,例如日期,有自己的一些彙總選項:最早、最新、第一個和最後一個。And special types of data, like dates, have a few of their own aggregate options: earliest, latest, first, and last.

在下列範例中:In the example below:

  • [銷售單位] 和 [製造價格] 是包含數值資料的資料行Units Sold and Manufacturing Price are columns that contains numeric data
  • [業別]、[國家/地區]、[產品]、[月份] 和 [月份名稱] 包含類別目錄資料Segment, Country, Product, Month, and Month Name contain categorical data

在 Power BI 中建立視覺效果時,會對某個類別欄位彙總數值欄位 (預設值是「總和」)。When creating a visualization in Power BI, numeric fields will be aggregated (the default is sum) over some categorical field. 例如,「依產品的銷售單位」、「依月份的銷售單位」和「依業別的製造價格」。For example, "Units Sold by Product, "Units Sold by Month" and "Manufacturing Price by Segment. 有些數值欄位稱為量值Some numeric fields are referred to as measures. 可以輕鬆地在 Power BI 報表編輯器中識別量值 -- 量值會在 [欄位] 清單中顯示 ∑ 符號。It's easy to identify measaures in the Power BI report editor -- measures are shown with the ∑ symbol in the Fields list. 如需詳細資訊,請參閱報表編輯器導覽For more information see The report editor... take a tour.

為什麼彙總運作的方式和我想要的不同?Why don't aggregates work the way I want them to?

使用 Power BI 服務中的彙總可能會造成混淆。或許您有數值欄位,但 Power BI 不讓您變更彙總。Working with aggregates in Power BI service can be confusing; maybe you have a numeric field and Power BI won't let you change the aggregation. 或是您有一個欄位,例如年份,您不想彙總它,只想要計算發生次數。Or maybe you have a field, like a year, and you don't want to aggregate it, you just want to count the number of occurrences.

大多數情況下,問題的來源是資料集中的欄位定義方式。Most often, the source of the problem is how the field was defined in the dataset. 欄位可能定義為文字,且其說明了它為何無法進行加總或平均。Maybe the field is defined as text and that explains why it can't be summed or averaged. 不幸的是,只有資料集擁有者可以變更欄位的分類方式Unfortunately, only the dataset owner can change the way a field is categorized. 因此如果您具有資料集的擁有者權限,不論是在 Desktop 或用來建立資料集的程式 (例如 Excel),您便可以修正這個問題。So if you have owner permissions to the dataset, either in Desktop or the program that was used to create the dataset (e.g., Excel), you can fix this problem. 否則,您必須連絡資料集擁有者以取得協助。Otherwise, you'll need to contact the dataset owner for help.

為了協助您度過混淆,我們在本文結尾有專門的一節,稱為考量與疑難排解To help you navigate the confusion we have a special section at the end of this article called Considerations and troubleshooting. 如果您在那裡找不到答案,請在 Power BI 社群論壇提出您的問題,以便得到直接來自 Power BI 小組的快速回應。If you don't find your answer there, post your question on the Power BI Community forum for a quick response directly from the Power BI team.

變更數值欄位的彙總方式Change how a numeric field is aggregated

假設您有加總不同產品銷售單位的圖表,但您比較想要平均值。Say you have a chart that sums the units sold for different products, but you'd rather have the average.

  1. 建立使用類別和量值的圖表。Create a chart that uses a category and a measure. 在此範例中,我們會使用「依產品的銷售單位」。In this example we're using Units Sold by Product. 根據預設,Power BI 會建立針對每個產品 (軸中的類別) 加總銷售單位 (值中的量值) 的圖表。By default, Power BI creates a chart that sums the units sold (measure in the Value well) for each product (category in the Axis well).

  2. 在 [視覺效果] 窗格中,以滑鼠右鍵按一下量值,然後選取您需要的彙總類型。In the Visualizations pane, right-click the measure, and select the aggregate type you need. 在此案例中,我們選取「平均值」。In this case, we're selecting Average. 如果您沒有看到您需要的彙總,請參閱底下的<考量與疑難排解>。If you don't see the aggregation you need, see "Considerations and troubleshooting" below.

    注意

    下拉式清單中可用的選項會因 1) 所選取的欄位,以及 2) 資料集擁有者分類欄位的方式而有所不同。The options available in the dropdown will vary depending on 1) the field selected and 2) the way that field was categorized by the dataset owner.

  3. 您的視覺效果現在使用依平均值彙總。Your visualization is now using aggregated by average.

彙總資料的方式Ways to aggregate your data

彙總欄位可用的某些選項:Some of the options that may be available for aggregating a field:

  • 不加總Do Not Summarize. 選擇了此選項,該欄位中的每個值會分開處理且不加總。With this option chosen, each value in that field is treated separately and not summarized. 如果有不應該加總的數值識別碼資料行,這很常用。This is often used if you have a numeric ID column that shouldn't be summed.
  • 加總Sum. 這會加總該欄位中的所有值。This adds all the values in that field up.
  • 平均Average. 求出值的算術平均值。Takes an arithmetic mean of the values.
  • 最小值Minimum. 顯示最小的值。Shows the smallest value.
  • 最大值Maximum. 顯示最大的值。Shows the largest value.
  • 計數 (沒有空格)。Count (Not Blanks). 這會計算該欄位中不是空白值的數目。This counts the number of values in that field that are not blank.
  • 計數 (相異)。Count (Distinct). 這會計算該欄位中不同值的數目。This counts the number of different values in that field.
  • 標準差。Standard deviation.
  • 變異數Variance.
  • 中位數Median. 顯示中間值。Shows the median (middle) value. 這是具有相同項目數以上或以下的值。This is the value that has the same number of items above and below. 如果有 2 個中位數,Power BI 會取其平均值。If there are 2 medians, Power BI averages them.

例如,下列資料:For example, this data:

國家/地區Country 數量Amount
美國USA 100100
英國UK 150150
加拿大Canada 100100
德國Germany 125125
法國France
日本Japan 125125
澳洲Australia 150150

會得到下列結果:Would give the following results:

  • 不加總:分別顯示每個值Do Not Summarize: Each value is shown separately
  • 加總:750Sum: 750
  • 平均:125Average: 125
  • 最大值:150Maximum: 150
  • 最小值:100Minimum: 100
  • 計數 (沒有空格): 6Count (Not Blanks): 6
  • 計數 (相異): 4Count (Distinct): 4
  • 標準差: 20.4124145...Standard deviation: 20.4124145...
  • 變異數: 416.666...Variance: 416.666...
  • 中位數: 125Median: 125

建立使用類別 (文字) 欄位的彙總Create an aggregate using a category (text) field

您也可以彙總非數值欄位。You can also aggregate a non-numeric field. 例如,如果有產品名稱欄位,您可以將它新增為值,然後將它設定為 [計數]、[相異計數]、[第一個] 或 [最後一個]。For example, if you have a product name field, you can add it as a value and then set it to Count, Distinct count, First, or Last.

  1. 在此範例中,我們已將 [產品] 欄位拖入 [值]。In this example, we've dragged the Product field into the Values well. [值] 通常用於數值欄位。The Values well is typically used for numeric fields. Power BI 會辨識這是一個文字欄位、將彙總設為 [不摘要],並呈現單一資料行的資料表。Power BI recognizes that this is a text field, sets the aggregate to Do not summarize, and presents us with a single-column table.

  2. 如果將彙總從預設的 [不摘要] 變更為 [計數 (相異)] ,Power BI 就會計算不同產品的數目。If we change the aggregation from the default Do not summarize to Count (Distinct), Power BI counts the number of different products. 在本案例中有 4 個。In this case, there are 4.

  3. 如果將彙總變更為 [計數],Power BI 就會計算總數。And if we change the aggregation to Count, Power BI counts the total number. 在本案例中,[產品] 有 7 個項目。In this case, there are 7 entries for Product.

  4. 藉由將相同的欄位 (在本案例為 [產品]) 拖入 [值],並維持預設彙總 [不摘要],Power BI 會依產品細分計數。By dragging the same field (in this case Product) into the Values well, and leaving the default aggregation Do not summarize, Power BI breaks down the count by product.

考量與疑難排解Considerations and Troubleshooting

問︰為什麼沒有不摘要選項?Q: Why don't I have a Do not summarize option?

答:您所選取的欄位可能是導出量值或在 Excel 或 Power BI Desktop 中建立的進階量值。A: The field you've selected is likely a calculated measure or advanced measure created in Excel or Power BI Desktop. 每個導出量值都有自己的硬式編碼公式。Each calculated measure has its own hard-coded formula. 您無法變更使用中的彙總。You can’t change the aggregation being used. 例如,如果是總和,就只能是總和。For example, if it’s a sum, it can only be a sum. 在 [欄位] 清單中,「導出量值」會以計算機符號顯示。In the Fields list, calculated measures are shown with the calculator symbol.

問︰我的欄位數值,為何我只能選擇 [計數] 和 [相異計數]?Q: My field is numeric, why are my only choices Count and Distinct count?

答 1︰可能的原因為資料集擁有者不小心或故意「不」將該欄位分類為數字。A1: The likely explanation is that the dataset owner has, accidentally or intentionally, not classified the field as a number. 例如,如果資料集具有 [年] 欄位,資料集擁有者可能會將其分類為文字,因為比較可能會計算 [年] 欄位 (例如在 1974 年出生的人數),而不是加總或取平均值。For example, if a dataset has a year field, the dataset owner may categorize that as text because it is more likely that the year field will be counted (i.e., number of people born in 1974) and not that it will be summed or averaged. 如果您是擁有者,可以在 Power BI Desktop 中開啟資料集,並使用 [模型] 索引標籤來變更資料類型。If you are the owner, you can open the dataset in Power BI Desktop and use the Modeling tab to change the data type.

答 2︰如果欄位有計算機圖示,這表示它是「導出量值」,而每個導出量值都有它自己的硬式編碼公式,只能由資料集擁有者變更。A2: If the field has a calculator icon, that means it's a calculated measure and each calculated measure has its own hard-coded formula that can only be changed by a dataset owner. 正在使用的計算可能是簡單的彙總,例如平均或加總,但它也可能更複雜,例如「在父類別所佔比重的百分比」或「自年初起的計算加總」。The calculation being used may be a simple aggregation like an average or sum, but it may also be something more complicated like a "percent of contribution to parent category" or "running total since start of the year". Power BI 並不會將結果加總或平均,但會改為針對每個資料點重新計算 (使用硬式編碼的公式)。Power BI isn't going to sum or average the results but will instead just re-calculate (using the hard-coded formula) for each data point.

答 3︰另一個可能的原因是,您將欄位放至只允許類別目錄值的「值區」。A3: Another possibility is that you've dropped the field into a bucket that only allows categorical values. 在此情況下,您只能選擇計數與相異計數。In that case, your only options will be count and distinct count.

答 4︰第三個可能的原因是,您使用欄位作為座標軸。A4: And a third possibility is that you're using the field for an axis. 例如,在橫條圖的軸上,Power BI 會針對每個相異值顯示一個橫條,它完全不會彙總欄位值。On a bar chart axis, for example, Power BI shows one bar for each distinct value -- it doesn't aggregate the field values at all.

注意

此規則的例外狀況是散佈圖,「需要」彙總 X 軸和 Y 軸的值。The exception to this rule is scatter charts, which require aggregated values for the X and Y axes.

問︰我有散佈圖,而且我希望我的欄位「不要」彙總。Q: I have a scatter chart and I want my field to not aggregate. 要怎麼做?How do I do this?

答︰將欄位新增至 [詳細資料] 值區,而不是 X 或 Y 軸的值區。A: Add the field to the Details bucket and not to the X or Y axes buckets.

問︰當我新增數值欄位至視覺效果時,它們大部分都預設為加總,但有些預設為平均或計數或其他彙總。Q: When I add a numeric field to a visualization, most of them default to sum but some default to average or count or some other aggregation. 為什麼預設彙總不一律相同?Why isn't the default aggregation always the same?

答︰資料集擁有者可以選擇設定每個欄位的預設摘要。A: Dataset owners have the option to set the default summarization for each field. 如果您是資料集擁有者,請變更 Power BI Desktop [模型] 索引標籤中的預設摘要。If you are a dataset owner, change the default summarization in the Modeling tab of Power BI Desktop.

問︰我是資料集擁有者,我想確定欄位永遠不會進行彙總。Q: I'm a dataset owner and I want to ensure that a field is never aggregated.

答:在 Power BI Desktop 中,請在 [模型] 索引標籤上,將 [資料類型] 設為 [文字]。A: In Power BI Desktop, in the Modeling tab, set Data type to Text.

問︰我在我的下拉式清單中看不見 [不摘要] 的選項。Q: I do not see Do not summarize as an option in my dropdown.

答︰請嘗試移除欄位,並將其新增回去。A: Try removing the field and adding it back in.

有其他問題嗎?More questions? 試試 Power BI 社群Try the Power BI Community