Hello and welcome back. Working with data can be quite challenging and even more so when numbers are involved. No need to worry, in this video, you will journey through aggregates to discover some easy ways to use aggregation methods to increase your skills even more. Have you ever wanted to combine mathematical values in your data? Well, that is called aggregation and can be done using sum, average, maximum, and count. The result of that mathematical operation is an aggregate, as simple as Pi. When Power BI service and Power BI desktop create visualizations, they may aggregate your data. Sometimes it's 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 Power BI uses in a visualization. First, let's take a look at data types because the type of data determines how and whether Power BI can aggregate it. Most data sets have more than one type of data. At the most basic level, that data is either numeric or it isn't. Power BI can aggregate numeric data using a sum, average, count, minimum, variance, and much more. The service can even aggregate textual data, often called categorical data. If you take a categorical field that consists of data that is in text format and you place it in a numeric-only bookers such as values. Power BI will count the occurrences of each category. No wonder, it's called business intelligence. You'll also come across special types of data, like dates that have their own aggregate options, such as earliest, latest, first, and last. Now that you know the difference between numerical and categorical data, let's take a look at a real-life example. In this table, the units sold and manufacturing price are the columns that contain numeric data, while the segment, contrary, product, month, and month name columns contain categorical data. You are getting quite good at this. When creating a visualization in Power BI, the service will aggregate numeric fields over some categorical fields. For example, units sold by product, units sold by month, and manufacturing price by segment. Power BI refers to some numeric fields as measures. These measures are calculations on your data and are easy to identify in the Power BI report editor. For example, when creating a visualization in Power BI, the service will automatically aggregate numeric fields. The default action is to sum the field. That field will have a Sigma symbol next to it in the field list to show that this has happened. This includes fields such as sales or units sold here. You can create customized measures using a formula language called Data Analysis Expression or DAX for short. You can name these measures whatever you want and add them to a new or existing visualization just like any other field. They will appear in the fields list with the calculator symbol, the Sigma beside them. Now let's look at how you can make aggregates work for you. Working with aggregates in Power BI can be confusing. You might find yourself in a situation where 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. Let's explore some of the reasons for this. It might happen that the owner of the data set defined the field as text, and that explains why Power BI cannot sum or average it. Only the owner of the data set can change the category of a specific field, but the good news is if you have owner permissions, you can do this yourself. Otherwise, you need to phone a friend, in this case, the owner of the data set, for help. Next, you look at ways to change how a numeric field is aggregated. Let's say you have a chart that sums up the unit sold for different products, but you'd rather have the average. You simply create a clustered column chart that 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 for each product. You simply have to drag the measure into the value well and the category into the access well, but what happens next? In the visualizations pane, right-click the "Measure" and select the aggregate type you need. In this case, we're selecting average. Remember that the options available in the drop-down list will vary depending on the fields that is selected, and the way that the data set owner has categorized that field. Your visualization is now using aggregated by average. Now that you know more about aggregating data, let's unpack some of the options that you can use to aggregate a field. When you choose to summarize data, Power BI will treat 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. You have many options to choose from. These include sum, average, minimum, and maximum. But wait, there's more. You can also select between count for the values in the fields that are not blank, count distinct for the number of different values in that field, standard deviation, and variance, and median, which shows you the middle value. This basically means that this value has the same number of items above and below. In the case where there are two medians, Power BI will average them for you. That's a wrap. Well done on completing this lesson. See you soon.
