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 SalesAverage Measure to the table report, and for more charts, Click Here. 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 Aggregate and the function article for the remaining Power BI functions.
Let me create three cards to display the Average Sales, Orders, and Profit in one report. Next, add horizontal and vertical Bar charts 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.
