What is an Aggregated Monthly Payment

Use aggregates (sum, average, and so on) in the Power BI service

  • 8 minutes to read

What is an aggregate?

You may want to mathematically combine values ​​in your data. This can be the math operation to find the sum, average, maximum, number, and so on. Combining values ​​in data is called aggregate designated. The result of this mathematical operation is a Aggregate.

If visualizations are created in the Power BI service and Power BI Desktop, data can be aggregated. Often times, the aggregate is already the values ​​you need, but in other cases you may want to aggregate the values ​​in a different way, such as with a sum instead of an average. There are several ways to manage and change the aggregate used in a visualization in Power BI.

First, consider the Data typesbecause the type of data determines how and whether it can be aggregated in Power BI.

Data types

Most datasets contain more than one data type. Basically, the data is either numeric or non-numeric. Power BI can aggregate numeric data as sum, average, count, minimum, variance, and much more. The service can even aggregate text data, which is often referred to as Category data designated. If you're trying to aggregate a category field by putting it in a purely numeric range like values or Tooltips Place, Power BI will count the occurrences of each category or the number of different occurrences of each category. Certain types of data, such as dates, have their own aggregation options: earliest, latest, first, and latest.

Consider the following example:

  • Units Sold and Manufacturing Price are columns with numeric data.

  • segment, Country, Product, Month and Month name contain category data.

When creating a visualization in Power BI, the service aggregates numeric fields using a category field (by default as total). Examples are: “Units Sold by Product _ "," Units Sold _by Month_ "And" Manufacturing Price _by segment_ “(Units sold by product, units sold by month, manufacturing costs by segment). Power BI refers to some numeric fields as _Measures. Measures are easy to spot in the Power BI report editor: The list Fields lists measures with the symbol "∑". For more information, see the introduction to the report editor in Power BI.

Why don't aggregates work as I imagine?

Working with aggregates in the Power BI service can be confusing. You might have a numeric field and Power BI won't let you change the aggregation. Or do you have a field (for example a year) that you do not want to be aggregated, but for which you just want to find the number of occurrences?

Usually the underlying problem is with the field definition in the dataset. The dataset owner may have defined the field as text, which would explain why Power BI cannot find the sum or the average. However, the categorization of a field can only be changed by the owner of the dataset. So if you have owner permissions on the dataset (in Power BI Desktop or in the program that created the dataset, such as Excel), you can fix the problem. Otherwise, you will need to contact the dataset owner for help.

At the end of this article you will find a separate section called Points to Consider and Troubleshoot. There you will find tips and instructions. If you can't find the right answer there, ask your question in the Power BI community forum. You will receive an immediate response directly from the Power BI team.

Change how a numeric field is aggregated

For example, suppose you have a chart that adds the units sold for different products. But you need the mean.

  1. Create a Column chart (grouped) using a measure and a category. In this example we use “Units Sold by Product”. By default, Power BI creates a chart showing the sum of units sold (drag the measure into the value) for each product (drag the category into the field axis).

  2. Click in the area Visualizations right-click the measure, and then select the type of aggregate you want. In that case, choose average out. If you don't see the aggregation you need, see the section below for more information Points to Consider and Troubleshoot.

    Note

    The options available in the drop-down list vary depending on the field you selected and how the dataset owner categorized the field.

  3. The visualization is now aggregated with an average value.

Ways to aggregate data

Here are some of the options that may be available to aggregate a field:

  • Do not summarize. When this option is selected, Power BI treats all values ​​in the field separately and does not group them together. Use this option if you have a numeric ID column that the service shouldn't be summarizing.

  • total. This option adds up all the values ​​in this field.

  • Average. Determines the arithmetic mean of the values.

  • minimum. Shows the smallest value.

  • maximum. Shows the greatest value.

  • Number (without spaces) . Enumerates the number of values ​​in this field that are not empty.

  • Number (discreet) . This option counts the number of different values ​​in this field.

  • Standard deviation.

  • Variance.

  • Median. Displays the median value (mean value). This value has the same number of values ​​above and below it. If there are 2 medians, Power BI takes an average.

The data:

countryAmount
United States100
UNITED KINGDOM150
Canada100
Germany125
France
Japan125
Australia150

lead to the following results:

  • Do not summarize: Each value is shown separately.

  • Total: 750

  • Average: 125

  • Maximum: 150

  • Minimum: 100

  • Number (without spaces): 6

  • Number (discreet): 4

  • Standard deviation: 20,4124145...

  • Variance: 416,666...

  • Median: 125

Create an Aggregate with a Category Field (Text)

You can also aggregate non-numeric fields. For example, if there is a field with product name, you can add it as a value and for it number, Discrete number, First or Last establish.

  1. Drag the box Product (Product) in the field values. The field values is usually used for numeric fields. Power BI recognizes that this field is a text field and the aggregate hangs up Do not summarize and displays a one-column table.

  2. If you change the aggregation from the default Do not summarize in Number (unique) change, Power BI counts the number of different products. In this case there are four.

  3. And if you do the aggregation in number change, Power BI counts the total number. In this case there are seven entries for Product (Product).

  4. By typing the same field (in this case Product) in the field values pull and the default aggregation Do not summarize take over, Power BI divides the number by product.

Points to Consider and Troubleshoot

Q: Why is the option Do not summarize not available?

A: The field you selected is likely a calculated measure in a multidimensional model created in Excel or Power BI Desktop. Each measure has its own hard-coded formula. You can't change the aggregation used by Power BI. For example, if it is a sum, it must remain a sum. In the list Fields become Measures shown with a calculator icon.

Q: My field is numerically. Why am I only given the options? number and Discrete number to disposal?

A1: The obvious explanation is that the owner of the dataset has the Not classified as a number. For example, if a dataset includes the year contains, the owner of the dataset can categorize this value as text. It's more likely that Power BI will field the year counts (e.g. the number of people born in 1974). Power BI is less likely to calculate the sum or the average. As the owner, you can open the dataset in Power BI Desktop and view the data type on the tab Modeling to change.

A2: If the field has a calculator icon, it is a Measure. Each measure has its own formula that only the owner of the dataset can change. The calculation used by Power BI can be a simple aggregation, such as B. an average or a sum. However, more complicated aggregations such as “percentage share in a superordinate category” or “running total since the beginning of the year” are also possible. Power BI does not calculate a sum or average for the results. Instead, the calculation is performed again for each data point (using the hard-coded formula).

A3: Another possibility: you have the field in one Bucket placed in which only category values ​​are allowed. In this case, only the options “Count” and “Distinct Count” are available.

A4: The fourth option is to use the field for an axis. For example, on the axis of a bar chart, Power BI displays one bar for each unique value. So the field values ​​are not aggregated at all.

Note

An exception are scatter charts, which have aggregated values ​​for the X and Y axes required are.

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

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

Q: I have a scatter plot and want to Notthat my field is aggregated. How can I achieve this?

A: Add the field to the bucket Details (and not the buckets for the X or Y axes).

Q: When I add a numeric field to a visualization, most of them are summed by default, but some are by default averaged, counted, or used some other aggregation. Why is the default aggregation not always the same?

A: Dataset owners can customize the default summary for each field. As the owner of a dataset, you can view the default summary on the Power BI Desktop tab Modeling to change.

Q: I own a dataset and want to make sure a field is not aggregated.

A: In Power BI Desktop, put on the tab Modeling the option Data type on text firmly.

Q: There is an option in the drop-down list Do not summarize not available.

A: Remove the field and then add it back.

Further questions? Contact the Power BI community