Working with aggregates (sum, average, etc.) in Power BI - Power BI (2023)

APPLIES TO:✔️PowerBIDesktop ✔️PowerBIservice

What is an aggregate?

Sometimes you want to mathematically combine values ​​in your data. The math operation could be sum, average, maximum, count, etc. When you combine values ​​in your data, it saysaggregate. The result of this mathematical operation is aAggregate.

When you create visualizations in Power BI Desktop and the Power BI service, they can aggregate your data. Often the aggregate is what you need, but sometimes you might want to aggregate the values ​​in a different way. For example, a sum versus an average. There are several ways to manage and change the aggregated Power BI usages in a visualization.

First, let's take a look at the dataThe typeas the nature of the data determines how and if Power BI can aggregate it.

types of data

Most records contain more than one data type. At the simplest level, the data is either numeric or not. Power BI can aggregate numeric data using sum, average, count, minimum, variance, and more. Power BI can even aggregate text data, often called "categoricallyData. If you're trying to aggregate a categorical field by packing it into a purely numeric bucket such asValuesortooltips, Power BI counts the occurrences of each category or the distinct occurrences of each category. Special data types like dates have some aggregation options of their own: earliest, newest, first, and last.

In the following example:

• units soldandmanufacturing priceare columns that contain numeric data.

• Segment,LandRegion,Product,Month, andmonth namecontain categorical data.

  

When you create a visualization, Power BI aggregates numeric fields (the default istotal) over a categorical field. Example: "Units soldby product", "Units soldafter months' and 'Production priceby segment". Power BI refers to some numeric fields asDimensions. It's easy to identify measures in the Power BI report editor - TheFelderList shows measures with the ∑ symbol next to them. SeeThe report editor... take a tourFor more information.

Why don't aggregates work the way I want them to?

Working with aggregates in Power BI 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 year and you don't want to aggregate it, just count the number of occurrences.

Typically, the underlying problem is the field definition in the dataset. The record owner may have defined the field as text, which explains why Power BI can't sum or average it. Unfortunately,Only the dataset owner can change a field's categorization. So if you have owner permissions for the dataset, either in desktop or in the program that created the dataset (e.g. Excel), you can fix this problem. Otherwise, you'll need to contact the dataset owner for help.

There is a special section at the end of this article calledConsiderations and Troubleshooting. There are tips and instructions. If you don't find your answer there, post your question on thePower BI-Community-Forum. You'll get a quick response directly from the Power BI team.

Change how a numeric field is aggregated

Suppose you have a chart that sums the units sold for different products, but you'd rather have the average.

1. A... createClustered Column Chartthat uses a measure and a category. In this example, we're using units sold by product. By default, Power BI creates a chart that sums the units sold (drag the metric into theWertgood) for each product (drag the category into theaxisGut).

   

2. In themvisualizationsIn the pane, right-click the measure and select the desired aggregate type. In this case we selectAverage. If you don't see the aggregation you need, look at itConsiderations and TroubleshootingSection.

   

   note

   The options available in the drop-down list depend on 1) the field selected and 2) how the dataset owner categorized that field.

   (Video) Power BI DAX Tutorial (5/50) - What is Sum and SumX

3. Your visualization now uses Aggregated by Average.

   

Ways to aggregate your data

Some of the options that may be available to aggregate a field:

• Don't summarize. When this option is selected, Power BI treats each value in this field separately and doesn't summarize them. Use this option when you have a numeric ID column that you don't want Power BI to sum.
• total. Adds all values ​​in this field.
• Average. Takes an arithmetic mean of the values.
• Minimum. Displays the smallest value.
• Maximal. Displays the largest value.
• Count (no spaces).Counts the number of values ​​in this field that are not blank.
• Count (unique).Counts the number of distinct values ​​in this field.
• standard deviation.
• variance.
• Median. Displays the median (mean value). This value has the same number of elements above and below. If there are two medians, Power BI averages them.

For example this data:

Would give the following results:

• Don't summarize: Each value is displayed separately
• total: 750
• Average: 125
• Maximal: 150
• Minimum: 100
• Number (no spaces):6
• Count (unique):4
• standard deviation:20.4124145...
• Deviation:416.666...
• Median:125

Create an aggregate with a category field (text field).

You can also aggregate a non-numeric field. For example, if you have a category name field, you can add it as a value and then set it toTo count,Unique Count,First, orLast.

1. pull thatCategoryfield on the report area. ThatValueswell is typically used for numeric fields. Power BI recognizes that this field is a text field, so it creates a table with a single column.

   

2. Select the arrow next toCategory, and change the default aggregationDon't summarizetocount (unique), Power BI counts the number of different categories. In this case it is three.

   

3. And if you change the aggregation toTo count, Power BI counts the total. In this case there are 24 entries forCategory.

   

   (Video) Power BI aggregate functions example | Power BI SUM and SUMX functions

4. Drag the same box (in this caseCategory) in thecolumnsAgain good. Keep the default aggregationDon't summarize. Power BI breaks down the count by category.

   

Considerations and Troubleshooting

Q: Why don't I have anyDon't summarizePossibility?

A: The selected field is likely a calculated measure in a multidimensional model, or a measure created in Excel, orPower BI-Desktop. Each measure has its own hard-coded formula. You can't change the aggregation used by Power BI. For example, if it's a sum, it can only be a sum. ThatFelderlist showsDimensionswith the calculator icon.

Q: My fieldisnumeric, why are my only optionsTo countandUnique Count?

A1: The likely explanation is that the dataset owner did thisNotClassified the field as a number. For example, if a record has aYearfield allows the dataset owner to categorize the value as text. It's more likely that Power BI will count themYearRange (e.g. number of people born in 1974). Power BI is less likely to sum or average it. If you're the owner, you can open the dataset in Power BI Desktop and use theModelTab to change the data type.

A2: If the field has a calculator icon, then it is ameasure up. Each measure has its own formula that only the owner of the record can change. The calculation used by Power BI can be a simple aggregation like an average or a sum. It can also be something more complicated, such as For example, "percentage of contribution to parent category" or "year-to-date running total." Power BI does not sum or average the results. Instead, it is simply recalculated (using the hard-coded formula) for each data point.

A3: Another possibility is that you put the field in aBucketwhich only accepts categorical values. In this case, your only options are Count and Distinct Count.

A4: And a fourth possibility is that you use the field for an axis. For example, on a bar chart axis, Power BI displays a bar for each unique value - the field values ​​are not aggregated at all.

Q: Why can't I aggregate text fields for SSAS (SQL Server Analysis Services) data sources?

A: Live connections to SSAS multidimensional models do not allow client-side aggregations, including first, last, avg, min, max, and sum.

Q: I have a scatterplot and I want my field to have oneNotaggregate. As?

A: Add the field todetailsblade and not on the X or Y axis blades.

Q: When I add a numeric field to a viz, 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 can set the default summary for each field. If you are a dataset owner, change the default summary to theModelPower BI Desktop tab.

Q: I'm a dataset owner and I want to make sure a field is never aggregated.

A: In Power BI Desktop, imModeltab, setdata typetoText.

Q: I don't understandDon't summarizeas an option in my drop down list.

A: Try removing and adding the field again.

More questions?Try the Power BI community