Power BI - Aggregation Functions in DAX: SUM, COUNT, MIN, MAX and AVERAGE

Introduction

Power BI comes with the powerful formula language Data Analysis Expression (DAX) which allows the implementation of custom calculations. There are numerous operators and functions available in DAX. One essential type of DAX functions are aggregation functions. These functions are essential for data analysis, especially when it comes to gaining meaningful insights from complex data structures. Aggregations can be, for example, the summation of values, the counting of occurrences, the calculation of minimum and maximum values or the calculation of average values. Exactly these calculations can be performed with the DAX functions SUM, COUNT, MIN, MAX and AVERAGE. In this tutorial, we will explore these aggregation functions, examine their syntax and look at hands-on examples.

Data

The data for this tutorial comes from an Excel file. In the following tutorial, we explain how to import Data from an Excel file into Power BI:

Power BI - Import Data from Excel file

Introduction In this tutorial, we want to import Data from a Excel file into Power BI. This can be easily done in the Power BI Desktop interface. Data Source Our data source is the Excel file student. The file contains the following data: Power BI Desktop In the following, we

Deep Learning Nerds | The ultimate Learning Platform for AI and Data ScienceData Analyst

We have loaded the data into Power BI without executing any transformations. The data looks as follows:

Aggregation Functions

Now, let's explore aggregation functions in DAX. In the following, we look at the aggregation functions SUM, COUNT, MIN, MAX and AVERAGE.

SUM

The SUM function calculates the sum of the numeric values in a column. The syntax of the SUM function looks as follows:

SUM(<column>)

Let's look at the components in detail:

In order to explain how the function is used in practice, let's look at the following hands-on example. In our example, we want to calculate the sum of the credit points. To do this, we apply the SUM function to the column "creditpoints".

First, we create a new measure.

We name the measure "Total Credit Points" and enter the DAX code.

Let's consider the DAX formula in detail:

Total Credit Points = SUM(student[creditpoints])

To visualize the result of the measure, we use a card visual:

As you can see, the sum of the values in the column "creditpoints" is 705.

COUNT

The COUNT function counts the number of non-blank values in a column. The syntax of the COUNT function looks as follows:

COUNT(<column>)

Let's look at the components in detail:

In order to explain how the COUNT function is used in practice, let's look at the following hands-on example. In our example, we want to count the number of credit points. To do this, we apply the COUNT function to the column "creditpoints"