Power BI Average

Average Function in Power BI

Some of the formulas in MS Excel are directly incorporated into Power BI as well, in fact, there is a group of formulas that are exactly the same both with MS Excel and Power BI. In this article, we are going to discuss one of the aggregate functions in Power BIAggregate Functions In Power BIWhen we combine or summarize the numerical data, it’s called “Aggregation“, so the output we get is called “Aggregate”. Common aggregation functions are “SUM, AVERAGE, MIN, MAX, COUNT, DISTINCTCOUNT”, and so on. To use these aggregate functions, we need some data, which should be alpha-numerical.read more i.e. AVERAGE DAX Function in detail.

What Does AVERAGE Function Do in Power BI?

This is very simple this will aggregate the column that we mention and takes the average value of numerical values. For example, if an employee has achieved 100 K USD sales value in 12 months then using the AVERAGE in excelAVERAGE In ExcelThe AVERAGE function in Excel gives the arithmetic mean of the supplied set of numeric values. This formula is categorized as a Statistical Function. The average formula is =AVERAGE(read more we can get the average sales amount for that particular employee.

Below is the syntax of the AVERAGE function in Power BI DAXPower BI DAXIn Power Bi, DAX stands for Data Analysis and Expression and is a functional language that represents all of the functions in Excel and Power BI. Formulas in Power BI can be created using the DAX language.read more.

Power BI Average Formula

Column is simply the numerical column that we need to take the average of.

Since the AVERAGE function takes only numerical values for its evaluation we need to be aware of non-numerical values and their behavior, below are the points to be kept in mind with the non-numerical values column.

Power-BI-Average

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Power BI Average (wallstreetmojo.com)

Examples of AVERAGE Function in Power BI

Below are examples of the average function in Power BI. You can download the workbook to use the same file as we used in this example.

You can download this Power BI Average Excel Template here – Power BI Average Excel Template

Example #1

Power BI Average Example 1

We are going to use the above data to demonstrate the AVERAGE function in Power BI, you can download the excel workbook to practice along with us.

Steps to use an Average function in Power BI are as follows.

  1. Upload the above data table to the Power BI file. This data table name is “Sales_Table”.


    Power BI Average Example 1-1

  2. First, insert table visual to show employee-wise sales values.


    Power BI Average Example 1-2

  3. Now we will try to find the average sales value per month of each employee. Right-click on the table and choose “New Measure”.


    Power BI Average Example 1-3

  4. For this new measure give the name “Average Sales per Month”.


    Power BI Average Example 1-4

  5. Open the AVERAGE function now.


    Power BI Average Example 1-5

  6. For this AVERAGE function we simply need to choose the column that we need to take the average of, so choose the “Sales” column.


    Power BI Average Example 1-6

  7. Close the bracket and hit enter key to get the result. Now measure is created in the table.


    Power BI Average Example 1-7

  8. Drag and drop this new measure to the existing table to get each employee average sales.


    Power BI Average Example 1-8

Here we have average sales for each employee.

One more thing you need to know here is without entering the AVERAGE DAX function also we can get the average total of each employee.

After dragging and dropping the “Sales” column to the field of the table or any other visual click on the drop-down list of the “Sales” column in the fields section.

Power BI Average Example 1-9

As soon as you click on this drop-down list we can see these options for this column.

Power BI Average Example 1-10

As of now “SUM” is the current set of actions is performed, so simply choose the option of “Average” to get the average value instead of overall summation of values.

Power BI Average Example 1-11

As soon as you choose this option our visual shows the average values not overall sales values.

Power BI Average Example 1-12

Example #2

Now scenario situation where you need to create an average measure in power BIMeasure In Power BIMeasures in POWER BI are a summarization of any data. It is essential to have a summary of any data or be it any representation of data. In POWER BI, there are tools to create subjective measures based on the data itself and name them.read more for each individual employee i.e. conditional average measure. For example, we need to find the average only for the employee “Ricky”.

So, this can be done by using CALCULATE and FILTER functions along with the AVERAGE function.

Right-click on the table and choose New Measure and give the name to this measure as “Avg of Ricky”.

Example 2

Open the CALCULATE function.

Example 2-1

Expression is nothing but the kind of calculation that we need to perform, in this case, we need to perform AVERAGE calculation, so open AVERAGE function now.

Example 2-2

Choose the necessary column that needs to be considered for average, in this case, the “Sales” column.

Example 2-3

Next, we need to apply the FILTER function to specify for which employee we need to take the average of, so open FILTER function.

Example 2-4

Mention the table name as “Sales_Table”.

Example 2-5

For Filter Expression choose the “Sales Person” column.

Example 2-6

To apply the filter for one specific employee i.e. “Ricky” put the equal sign and enter the employee name in double-quotes.

Example 2-7

Ok, that’s all close two brackets and hit enter key to get the result.

Example 2-8

Now to see how Average DAX measure functioning in power BI will drag and drop this new measure to the existing table.

Example 2-9

As you can see above only for the employee “Ricky” new measure showing the average value.

Note: Power BI dashboard file can also be downloaded from the link below and the final output can be viewed.

You can download this Power BI Average Template here – Power BI Average Template

Things to Remember

  • Empty cells are ignored and cells with zero value are considered for average calculation.
  • Conditional average performed along with CALCULATE and FILTER function.

This has been a guide to Power BI Average. Here we learn how to use average Power BI measures with examples as well as using average Dax formula in Power BI. You may learn more about Power BI from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>