# Understanding Power BI Aggregations

Now, let’s delve deeper into one of the most important concepts in Power BI calculations â€” the aggregations.

Power BI aggregations are formulas used to calculate a single summarized value from multiple rows that are grouped together.

There are different ways to create aggregations in Power BI. Some of the examples of aggregation functions are the AVERAGE, COUNT, MIN, and MAX functions.

In the next sections, I’ll discuss some of them and explain how the said aggregations work.

## Using Power BI Aggregations For Simple Calculations

In the example below, you can see that the formula for Total Sales contains a SUM function. This is an example of an aggregating formula.

To create another aggregating formula, click New Measure and use it to calculate for the Average Sales.

Obviously, youâ€™ll have to use the AVERAGE function which is one of the most common examples of aggregations.

After creating the new measure, drag it to the table. You need to look at the initial context to clearly understand the average sales data. You can find the initial context from the City filter as well as the Date slicer.

If you click Hamilton from the City filter, youâ€™ll know that the results you see in the table are from that particular city.

In another example, letâ€™s calculate the total transactions using the COUNTA function. You have to use COUNTA instead of COUNT function because the Order Number column is considered a text column.

After creating the formula, drag the said measure to the table to see the results.

Placing it beside the Average Sales table can help you compare the total sales for the corresponding number of transactions.

Now, why is the COUNTA function considered an aggregation function? Well, it is an aggregating formula because it is the first context that is added before considering the other calculation engines.

For example, thereâ€™s no filter that is selected from the City filter so the only initial context is the dates. Therefore, the results on the 23rd of May, 2018 are based on all the available cities.

## Working Out The Data Model

Now, letâ€™s take a look at the data relationships in the model based on the formula for Total Sales.

In the data model, youâ€™ll see that the filter comes from the Dates table going to the Sales table through a filter propagation.

Once the said filter is active, the Sales table runs its evaluation to calculate the total revenue. This is because the formula references the Total Revenue column in the Sales table.

The SUM aggregation function evaluates every single row of the Total Revenue column after the initial context.

The same logic is true for the AVERAGE function. It evaluates all of the sales in the current context (e.g. date) then looks at every row of the Total Revenue column before getting its average.

In addition to that, the formula for Total Transactions works the same. The COUNTA function evaluates all the rows under the Order Number column. After that, it counts the numbers based on the initial context.

Thatâ€™s basically how aggregations work inside Power BI in terms of DAX calculations.

## Using Other Power BI Aggregations

Another set of aggregating formulas that you can encounter are the MIN and MAX functions.

To give you an example of how these aggregation functions work, letâ€™s make a new measure.

Name the measure as Max Sales and use the MAX function to calculate the maximum total revenue.

Again, drag the new measure and place it inside the table. As you have noticed, the formula still references the Total Revenue column of the Sales table. But this time, the results have changed because we now use a different function.

When you select a city from the filter, for example the Turanga region, the data in the table also changes. This is because the MAX function summarizes the values based on the given context.

## Conclusion

Learning about aggregations is one of the crucial steps in understanding how DAX calculations work.

Most of the formulas are easy to run inside an aggregating formula. Things will only start to get trickier once you start using iterating formulas.

In the next tutorial, let’s learn about iterating functions and how to use it for more flexible calculations.

I hope you’ve picked up some helpful tips here. I can’t wait to show you more in our next tutorial.

Thanks!

Sam

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, youâ€™ll learn the general strategies to improve your DAX or M capabilities. This is an...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...

## Power BI Ranking In Hierarchical Form

Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit...

## Using Python In Power BI | Dataset And String Function

In this tutorial, we'll discuss how you can create and prepare Dataset and String Function using Python...

## Announcing The Enterprise DNA Submit A Showcase Program

Today we are launching a brand-new program here at Enterprise DNA. We are looking to collaborate with...

## Inventory Management Reports To Show Trends In Sales

This Enterprise DNA Power BI Showcase focuses on Inventory Management. You may watch the full video of...

## Huff Gravity Model Analysis in Power BI

In this tutorial, we'll learn how to do a Huff Gravity Model analysis in Power BI. We can use this...

## Excel Hacks Every Business Should Know

No matter what industry you belong to, having a better understanding of Microsoft Excel gives you a...

## Power BI Report Example For An Optical Dataset

A lot of you may know that we have an ongoing Power BI Challenge. One of our recent Power BI report...

## AVERAGEX: Calculating Average Per Day In Power BI

Here I'm going to show you how to use the function AVERAGEX with DAX in Power BI. You may watch the...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...