Average Function in Power BI

Some of the formulas in MS Excel are also directly incorporated into Power BI. In addition, a group of formulas is the same with MS Excel and Power BI. In this article, in detail, we will discuss one 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, the AVERAGE DAX function.

What Does AVERAGE Function Do in Power BI?

It will aggregate the column we mention and take the average value of numerical values. So, for example, if an employee has achieved a $100,000 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.

The column is the numerical column 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.

  • Any column which contains non-numerical values is ignored for aggregation, and the DAX function returns the blank result.Logical results like “TRUE” and “FALSE” are ignored from the calculation in Power BICalculation In Power BIPower BI Calculate serves as a base function to apply other DAX functions in various scenarios. Assume you wish to apply a filter to get the average sales for a specific location; you may use the CALCULATE function to apply the filter and arrive at calculations.read more.The empty cells are ignored from the calculation. The cells with 0 are considered for calculation purposes.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor 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 using the same file we used in this example.

Example #1

We will use the above data to demonstrate the AVERAGE function in Power BI. You can download the Excel workbook to practice along with us.

We have average sales for each employee.

  • Upload the above data table to the Power BI file. This data table name is “Sales_Table.” First, insert a table visuals to show employee-wise sales values. Now, we will try to find each employee’s monthly average sales value. Right-click on the table and choose “New Measure.” We will name this new measure, the “Average Sales Per Month.” Open the AVERAGE function now. For this AVERAGE function, we need to choose the column we need to take the average of, so we choose the “Sales” column. Close the bracket and press the “Enter” key to get the result. Now, the measure is created in the table. Drop this new measure to the table to get each employee’s average sales.

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 dropdown list of the “Sales” column in the “Fields” section.

On clicking on this dropdown list, we can see these options for this column.

Currently, “Sum” is the current set of actions performed, so choose the option of “Average” to get the average value instead of the overall summation of values.

When you choose this option, the visual shows the average, not the overall sales values.

Example #2

Now, 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 employee, i.e., a conditional average measure. For example, we need to find the average only for the employee “Ricky.”

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

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

Open the CALCULATE function.

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

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

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

Mention the table name as “Sales_Table.”

For Filter Expression,choose the “Sales Person” column.

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

Close two brackets and press the “Enter” key to get the result.

We will drag and drop this new measure into the existing table to see how the AVERAGE DAX measure functions in Power BI.

You can see above that only the employee “Ricky” new measure shows the average value.

Note: We can also download the Power BI dashboard file from the link below. We can view the final output.

Things to Remember

  • The empty cells are ignored. The cells with zero value are considered for average calculation.We can perform the conditional average along with CALCULATE and FILTER functions.

This article has been a guide to Power BI Average. Here, we learn how to use average Power BI measures with examples and the AVERAGE DAX formula in Power BI. You may learn more about Power BI from the following articles: –

  • Power BI SUMMARIZEDrillthrough in Power BICreate Bookmarks in Power BICreate Dashboard in Power BI