Implement Aggregate Functions

Aggregate Functions are used when we want to mathematically combine values in your data. Now, this mathematical operation covers sum, average, maximum, count, and more. Therefore, when we combine values in the data, it’s referred to as aggregating. Also, the resulting output of the mathematical operation is an aggregate. Now, when Power BI service and Power BI Desktop create visualizations, they may aggregate your data. Moreover, the aggregate is just what you need, but may times we want to aggregate the values in a different way.

Types of Data

The data is either numeric or it is not at a very basic level.

• Firstly, Power BI can aggregate numeric data using a sum, average, count, minimum, variance, and much more. Moreover, the service can even aggregate textual data, often called categorical data.
• Secondly, special types of data, like dates, have a few of their own aggregate options: earliest, latest, first, and last.
• Also, when creating a visualization in Power BI, the service will aggregate numeric fields (the default is sum) over some categorical field. 

Steps to Change how a numeric field is aggregated

• Firstly, create a Clustered column chart that uses a measure and a category.
• Secondly, in the Visualizations pane, right-click the measure, and select the aggregate type you need.
• Next, 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, Power BI treats each value in that field separately and doesn’t summarize them. Use this option if you have a numeric ID column that the service shouldn’t sum.
• Sum. 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). Counts the number of values in that field that aren’t blank.
• Count (Distinct). Counts the number of different values in that field.
• Standard deviation.
• Variance.
• Median. Shows the median (middle) value. This value has the same number of items above and below. If there are two medians, Power BI averages them.

Steps to Create an aggregate using a category (text) field

• Firstly, drag the Product field into the Values well. The Values well is typically used for numeric fields. Then, Power BI recognizes that this field is a text field, sets the aggregate to Do not summarize, and presents you with a single-column table.
• Next, if you change the aggregation from the default Do not summarize to Count (Distinct), Power BI counts the number of different products. In this case, there are four.
• Then, and if you change the aggregation to Count, Power BI counts the total number. In this case, there are seven entries for Product.
• Lastly, 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.

For more details visit Work with aggregate functions (sum, average, and so on) in the Power BI service