## What is Aggregate Functions in Power BI?

Aggregate functions in Power BI are used to calculate a set of values within a column or a table and return a single aggregated result. These functions are essential for summarizing and analyzing data, especially with large datasets. Power BI offers several built-in aggregate functions that help users create meaningful insights and visualizations from their data.

##### Table of contents

###### Key Takeaways

- Aggregate functions are essential for summarizing and analyzing data in Power BI.
- They allow you to calculate totals, averages, counts, and other summary metrics.
- Use appropriate aggregate functions based on the nature of your data and the insights you’re trying to gain.
- Combine aggregate functions with visualizations to create meaningful reports and dashboards.

### Examples of Aggregate Functions in Power BI

In this section, we will apply different Aggregate functions in Power BI to create data visualization in Power BI.

#### #1 – SUM

In this example, we will calculate the total marks scored in a subject using the aggregate function SUM in Power BI using the **Student Exam Data** dataset. Student Exam Data contains scores for each student per subject. To create a visual using the SUM function in Power BI, follow the steps highlighted below:

**Step 1:** Open the Power BI Desktop, import the **Student Exam Data** dataset using the **Get Data** option, and click the **Load** button. You can choose the **Transform Data** option to perform data cleaning, filtering, and transformation as needed.

Once the data is loaded, you shall be able to view it by navigating to the **Fields** pane.

**Step 2:** Navigate to the **Fields** pane, right-click on the **Student Exam Data** table, and choose the **New column** option from the menu.

**Step 3**: Navigate to the **Formula** tab, and enter the Power BI DAX aggregate functions for the SUM function.

Here we have created a calculated column **Total Marks Obtained Per Subject** to calculate the total marks scored in a subject.

**Total Marks Obtained Per Subject = CALCULATE(SUM(‘Student Exam Data'[Exam points]),FILTER(ALLSELECTED(‘Student Exam Data’),’Student Exam Data'[Exam name] = EARLIER(‘Student Exam Data'[Exam name])))**

**Note:** We have used the aggregate functions DAX, such as Power BI CALCULATE, FILTER, and EARLIER along with the SUM function. It is to calculate the SUM in the context of the table filter.

- EARLIER is a filter function primarily used for nested calculations by taking specific values as input and then producing calculations based on that input.
- CALCULATE is another filter function that evaluates an expression in a modified filter context.

**Step 4:** Once the DAX is specified, click on the **Commit** icon to save the changes. This shall create a new column in the data table.

You can see the underlying values of this new column by navigating to the **Data** view tab.

**Step 5:** Navigate to the **Fields** pane to view the newly created column.

#### #2 – AVERAGE

In this example, we will extend the previous example of the SUM function to calculate the average marks scored in a subject using the AVERAGE function. To use the AVERAGE function in Power BI, follow the steps highlighted below:

**Step 1:**

- Navigate to the
**Fields**pane. - Right-click on the
**Student Exam Data**table. - Choose the
**New column**option from the menu.

**Step 2:** Navigate to the formula tab, and enter the Power BI aggregate functions DAX for the AVERAGE function.

Here we have created a calculated column **Average Marks Obtained** to calculate the average marks scored in a subject.

Average Marks Obtained = CALCULATE(AVERAGE(‘Student Exam Data'[Exam points]),FILTER(ALLSELECTED(‘Student Exam Data’),’Student Exam Data'[Exam name] = EARLIER(‘Student Exam Data'[Exam name])))

**Step 3:** Click the **Commit** icon to save the changes once the DAX is specified. It shall create a new column in the data table.

**Step 4:** Navigate to the **Fields** pane to view the newly created column.

#### #3 – COUNT

In this example, using the COUNT function, we will use the **US_Regional_Sales_Data** to calculate the total number of orders placed per customer across the stores. **US_Regional_Sales_Data **contains** **the sales information across various stores in the US.** **To use the COUNT function in Power BI, follow the steps highlighted below:

**Step 1:** Navigate to the Fields pane, right-click on the US_Regional_Sales_Data table, and choose the New column option from the menu.

**Step 2:** Navigate to the formula tab, enter the DAX enter the Power BI aggregate functions DAX for the COUNT function.

Here, we have created a calculated column, **Total Number of Orders Placed Per Customer,** to calculate the total number of orders placed per customer across various stores.

**Total Number of Orders Placed Per Customer = CALCULATE(COUNT(US_Regional_Sales_Data[OrderNumber]), FILTER(US_Regional_Sales_Data,US_Regional_Sales_Data[_CustomerID] = EARLIER(US_Regional_Sales_Data[_CustomerID])))**

**Step 3:** Click the **Commit** icon to save the changes once the DAX is specified. It shall create a new column in the data table.

**Step 4:** Navigate to the **Fields** pane to view the newly created column.

#### #4 – MIN & MAX

In this example, we will calculate the minimum and maximum marks scored in a subject using Student Exam Data. To use the MIN and MAX functions in Power BI, follow the steps below:

**Step 1:**

- Navigate to the
**Fields**pane. - Right-click on the
**Student Exam Data**table. - Choose the
**New column**option from the menu.

**Step 2:** Navigate to the formula tab, and enter the Power BI aggregate functions DAX for the MIN function and the other one for the MAX function.

Here we have created calculated columns **Minimum Marks Obtained and Maximum Marks Obtained** in a subject.

**Minimum Marks Obtained = CALCULATE(MIN(‘Student Exam Data'[Exam points]),FILTER(‘Student Exam Data’, ‘Student Exam Data'[Exam name] = EARLIER(‘Student Exam Data'[Exam name])))**

**Maximum Marks Obtained = CALCULATE(MAX(‘Student Exam Data'[Exam points]),FILTER(‘Student Exam Data’, ‘Student Exam Data'[Exam name] = EARLIER(‘Student Exam Data'[Exam name])))**

**Step 3:** Once the Power BI aggregate functions DAX is specified, click the **Commit** icon to save the changes. It shall create new columns in the data table.

**Step 4:** Navigate to the **Fields** pane to view the newly created column.

#### #5 – DISTINCTCOUNT

In this example, we will use the US_Regional_Sales_Data dataset to calculate the distinct number of customers placed across various stores using the DISTINCTCOUNT function, one of the Power BI aggregate functions for text. US_Regional_Sales_Data contains the sales information across various stores in the US. To use the DISTINCTCOUNT function in Power BI, follow the steps highlighted below:

**Step 1:** Navigate to the **Fields** pane, right-click on the **US_Regional_Sales_Data** table, and choose the **New column** option from the menu.

**Step 2:** Navigate to the formula tab, and enter the Power BI aggregate functions DAX for the DISTINCTCOUNT function.

Here we have created a calculated column **Distinct Number of Customers**.

**Distinct Number of Customers = DISTINCTCOUNT(US_Regional_Sales_Data[_CustomerID])**

**Step 3:** Once the DAX is specified, click on the **Commit** icon to save the changes.

**Step 4:** Navigate to the **Fields** pane to view the newly created column.

Once you have created the columns using the aggregated functions, navigate to the **Visualizations** pane, choose **Table** visual, and drag and drop the columns to the report canvas.

It will create a visualization in Power BI, as shown below.

You can validate the numbers by creating a Pivot table in Excel and comparing the numbers generated in Power BI.

Note that there are no Power BI custom aggregation functions currently supported in Power BI.

### Important Things to Note

- Aggregate functions can be applied to numeric and text columns, but the results might not be meaningful for text columns (e.g., average of text values).
- Aggregation can be affected by filters and slicers applied to the visualizations.
- Calculated columns and measures can also be used with aggregate functions to perform more complex calculations.

### Frequently Asked Questions (FAQs)

**1. What is difference between SUM and SUMX functions in power BI?**

**SUM:**

• The SUM function in Power BI is a basic aggregation function that calculates the sum of a numerical column within a table or a filter context. Its syntax is as follows:

SUM(ColumnName)

• It doesn’t involve iterating through rows; instead, it directly summarizes the values in the specified column based on the current filter context.**SUMX:**

• The SUMX function, on the other hand, is an iterator function. It iterates through each row of a table, evaluates a given expression for each row within the filter context, and then sums up the results of those individual expressions.

• The Syntax of the SUMX function is as follows:

SUMX (Table, Expression)

• This function is used when you need to perform a calculation that involves considering each iteration.

**2. What is difference between COUNT and COUNTA functions in power BI?**

The difference between COUNT and COUNTA functions are highlighted below:**COUNT:**• The COUNT function in Power BI is a basic aggregation function that counts the number of rows for a column containing non-blank values.

• The syntax of the COUNT function is as follows:

**COUNT(ColumnName)**

• The COUNT function considers rows that have Numbers, Dates, or Strings values.

**COUNTA:**

• The COUNTA function is similar to the COUNT function and count the number of rows for a column with non-blank values.

• The syntax of COUNTA function is as follows:

**COUNTA(ColumnName)**

**3. What is difference between AVERAGE and AVERAGEA functions in Power BI?**

Some of the key differences between AVERAGE and AVERAGEA in Power BI are highlighted below:

**4. What is difference between DISTINCTCOUNT and COUNTROWS functions in Power BI?**

The differences between DISTINCTCOUNT and COUNTROWS functions are highlighted below:**DISTINCTCOUNT:**

• It counts the total number of distinct values present in a column. It takes only a single argument i.e. column name and supports all the data type present in the column.

• The syntax of the DISTINCTCOUNT function is as per below:**DISTINCTCOUNT (ColumnName)**

**COUNTROWS:**

• It counts the number of rows in the table or in a table defined by an expression as specified in the argument.

• The syntax of the COUNTROWS function is as per below:

**COUNTROWS ([Table])**

### Download Template

This article must help understand **Power BI Aggregate Functions** with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to **Power BI Aggregate Functions**. Here we look into different aggregate function such as SUM, AVERAGE, COUNT, MIN & MAX, DISTINCTCOUNT, with examples. You can learn more from the following articles –

## Leave a Reply