The Power BI AVERAGE function is one of the DAX aggregate functions useful to find the arithmetic mean or average of all the numeric values in a given column. This article explains the POWER BI DAX AVERAGE function to calculate the arithmetic mean and returns a decimal number and the syntax of the AVERAGE function is as shown below:
AVERAGE(<Column>)
The Power BI DAX AVERAGE function works only on the numeric values. If you assign the non-numeric values as the argument:
- If it contains text or string data, the AVERAGE function returns blanks and no aggregation will perform.
- Rows with zeros are also included to calculate the average.
- If any row contains empty cells or logical values, those are ignored. It means, when a row contains 0, it is treated and counted as the row among the total rows. However, if it is an empty cell, it won’t count in the total rows.
Power BI DAX AVERAGE function Example
As we mentioned earlier, the AVERAGE function works on numeric columns (measures) and to assign the average, we need a Measure. So, within the Home tab, click the New Measure button and rename it as SalesAverage. To demonstrate the Power BI DAX AVERAGE function, use the Sales from the SuperStore Orders table to obtain the sales average.
SalesAverage = AVERAGE(Orders[Sales])
Please add the new Calculated Measure, named SalesAverage, to the Power BI table report. For more charts, please refer to the Power BI Charts article. Please don’t confuse it with the result of SalesAverage. For example, the Furniture Category has four subcategories and a total of 6128 products. So, the sum of sales = 7,31,893.31 divided by a total of 6128 gives 119.43.
The other option is to add the Profit Measure to the table. Next, click the down arrow beside the Sum of Profit and change the default Power BI DAX aggregate function from Sum to Average as shown below. Please refer to the List of Power BI Aggregate functions and the Power BI DAX functions article available on the Power BI tutorial page for the remaining functions.
Let me create three Power BI cards to display the Average Sales, Orders, and Profit in one report. Next, add horizontal and vertical Bar charts in Power BI to show the sales average by sub-category, average sales by category, and profit average by country.
You can click any report to see or highlight the average of them. For instance, click on the Office Supplies category to see the average product sales, Orders, and Profit belonging to Office Supplies. Refer to the DAX SUM and DAX MEDIAN functions.
