Power BI average function with Examples

In this Power bi tutorial, we will learn the Power bi Average function, which is an aggregation function. And also we will discuss the below points

• Power BI AVERAGE function
• Power Bi AVERAGEA function
• Power Bi AVERAGEX function
• When to use Power Bi AVERAGE, AVERAGEA, and AVERAGEX function.
• Power bi AVERAGE function example

Power Bi AVERAGE function

The Power Bi AVERAGE function returns the average i.e arithmetic mean of all the numbers in a column.

Whereas the Power Bi returns a decimal number that represents the arithmetic mean of the numbers in a column

The Power Bi AVERAGE function takes a specified column as an argument and finds the average of the values in that column.

Non-numeric values in the column are handled by this function as follows:

• If the column contains text, no aggregation will be done and the functions returns blank.
• If the column contains empty cells or logical value, then it returns blank
• If the columns cells contains 0 are included.

While using the average function you must keep in mind the difference between empty cells and the cells contains 0 values.

The Syntax for Power BI AVERAGE function

Average = AVERAGE(<column>)

Read Power bi measure examples

Power Bi AVERAGEA function

The Power bi AVERAGEA function returns the average that is the arithmetic mean of the values in a column and also it handles text and non-numeric values.

The Power Bi AVERAGEA function takes a column and averages the number in it, but also handles non-numeric data types according to the following rules:

• Values that evaluates to true, then the AVERAGEA function count as 1.
• Values that evaluates to false, then the AVERAGEA function count as 0.
• Values that contains non-numeric text, then the AVERAGEA function count as 0.
• If there is empty text(” “), then count as 0.

The syntax of the AVERAGEA function in Power BI:

AverageA = AVERAGEA(<column>)

Read Power bi measure count with filter

Power Bi AVERAGEX function

The Power bi AVERAGEX function calculates the average that is the arithmetic mean of a set expression evaluated over a table. Both the table and expression argument is required in this function.

Whereas the table parameter in this function contains the name of a table and the expression parameter contains a scalar result, which will be evaluated for each row of the table. It returns a decimal number.

The AVERAGEX function does not include non-numeric cells or nun cells.

The syntax of the AVERAGEX function in Power BI:

AverageX = AVERAGEX(<table>,<expression>)

When to use Power Bi AVERAGE, AVERAGEA, and AVERAGEX function

Here we will see when to use AVERAGE, AVERAGEA, and AVERAGEX function in Power bi desktop.

We will use the below sample data to get understand when to use Average, AverageA, and AVERAGEX function.

AVERAGE Function

We will use the AVERAGE Function, when:

• We want to find the average of the values in that column.
• Cells with value as 0 are included.
• Data type is numeric/integer/Decimal.
• Does suppoert logical value and text value.

Let’s see the Average function with an example

Average function of number data type

Here we will see how to calculate the average of amount using measure in power bi.

To calculate the average of amount, write the below measure:

Measure = AVERAGE('Table'[Amount])

AVERAGEA Function:

We will use the Power BI AVERAGEA Function when:

• The column is boolean type, and Number type.
• The column is empty text, it count as 0.
• And also it supports non-numeric text

Let’s understand AVERAGEA function with examples.

AVERAGEA function of boolean type

Here we will see how to find the average of a boolean column using measure in power bi desktop.

To calculate the Average of boolean, write the below measure:

Measure = AVERAGEA('Table'[Boolean ])

As per sample dataset we have 3 true value and 2 false value, So total sum of column values are 3 and number of values are 5. So, it will divide with 3 with 5 and answer will be 0.60.

AVERAGEA of Number type

Here we will calculate the average of amount using measure in power bi desktop.

To calculate the average of amount, write the below measure:

Measure = AVERAGEA('Table'[Amount])

AVERAGEX Function:

We will use the AVERAGEX function, when

• The value is numeric data type
• It does not support the text data type and boolean data type
• We want to use the filter condition.

Let’s see the AVERAGEX function with an example:

AVERAGEX of numerical data type:

Here we will find the Average of amount using measure in power bi desktop.

To calculate the Average of amount, write the below measure:

Measure = AVERAGEX('Table','Table'[Amount])

AVERAGEX function with filter condition:

Here we will see how to find the average of amount of product A using measure in power bi desktop.

To calculate the average of amount of product A write the below measure:

Measure = AVERAGEX(FILTER('Table', 'Table'[Product name]= "A"), 'Table'[Amount])

Read Power bi measure count with filter

Power bi AVERAGE function example

Here we will see the different examples of Power bi AVERAGE, AVERAGEA, and AVERAGEX function in power bi desktop.

Example 1: Average Sales per month

Here we will see how to calculate average sales per month using measure in power bi desktop.

We will use the below sample table, having three column sales date, sales, and sales person.

To calculate the average sales per month, write the below measure:

Average sales per month = AVERAGE(Data[Sales])

In the below screenshot, you can see the average sales per month.

Read Microsoft Power BI Card – How to use

Example 2: Average sales for sales person

Here we will see how to calculate the Average sales for the salesperson name john using measure in power bi desktop.

We will use below sample table below having three column salesperson, sales date, and sales

To calculate the average sales for the salesperson for john, write the below measure:

Average sales = AVERAGEX( FILTER( Data, Data[Sales person] ="John"), Data[Sales])

In the below screenshot, you can calculate the average sales for per person.

Read Microsoft Power BI KPI Visual

Example 3: Average per category

Here we will see how to calculate the average billed per profession using AVERAGEX() in measure in the Power BI desktop.

We will use the below sample data having three columns Name, profession, and price.

To calculate the average billed per profession, write the below measure:

Average Total Bille Per Profession = 
CALCULATE(
    AVERAGEX(
        SUMMARIZE( sheet, sheet[Profession], sheet[Name] ),
        CALCULATE( SUM( sheet[Price] ) )
    ),
    ALLEXCEPT( sheet, sheet[Profession] )
)

Example 4: Power bi average last 7 days

Here we will see how to calculate the average price for the last 7 days using the measure in power bi desktop.

We will use the below sample data having two column dates and price columns.

To calculate the average for the last 7 days, write the below measure:

measure 2 = IF(COUNTROWS(DATESINPERIOD('Table 2'[Date],LASTDATE('Table 2'[Date]),-7,DAY))>2,CALCULATE(AVERAGE('Table 2'[Price]),DATESINPERIOD('Table 2'[Date],LASTDATE('Table 2'[Date]),-7,DAY)))

You may like the following Power BI tutorials:

• Power BI MAX and MIN function with Examples
• Power BI DAX Logical functions
• Power BI DAX SUM and SUMX function – How to use
• Microsoft Power BI Combo Chart
• How to use Microsoft Power BI Scatter Chart

In this Power bi tutorial, we will learn about the Power bi AVERAGE, AVERAGEA, and AVERAGEX aggregation function with examples. And also we will discuss the below points:

• Power BI AVERAGE function
• Power Bi AVERAGEA function
• Power Bi AVERAGEX function
• When to use Power Bi AVERAGE, AVERAGEA, and AVERAGEX function.
• Power bi AVERAGE function example