In this article, we will explore the various ways in which the AVERAGE function can be used in Power BI and how it can be applied to different data sets.
Syntax of the AVERAGE Function
The syntax of the AVERAGE function is as follows:
Where `❰column❱` refers to the column that you want to calculate the average of.
For example, if you want to calculate the average of the sales column in your data model, you would use the following formula:
Using the AVERAGE Function in Power BI
The AVERAGE function can be used in two ways in Power BI: as a measure or as a calculated column.
Using the AVERAGE Function as a Measure
When using the AVERAGE function as a measure, you can calculate the average of a set of values based on a particular filter or context.
For example, if you want to calculate the average sales of a particular product, you would create a measure using the following formula:
Product Average Sales = AVERAGE(Sales)
You can then drag and drop this measure onto a visual, such as a table or a chart, and the average sales will be displayed based on the selected filter or context.
Using the AVERAGE Function as a Calculated Column
When using the AVERAGE function as a calculated column, you can calculate the average of a set of values and store the result in a new column.
For example, if you want to calculate the average sales for each product, you would create a calculated column using the following formula:
Product Average Sales = AVERAGE(Product[Sales])
This will create a new column in the Product table, which will contain the average sales for each product.
Using the AVERAGE Function with Other DAX Functions
The AVERAGE function can also be used in combination with other DAX functions to create more advanced calculations.
For example, you can use the AVERAGE function with the IF function to calculate the average sales of a particular product category. The formula would be as follows:
Category Average Sales = AVERAGE(IF(Product[Category] = "Category A", Product[Sales]))
This formula will calculate the average sales of products in Category A.
The AVERAGE function is a powerful tool that can be used to calculate the average of a set of values in Power BI. It can be used in various scenarios and can be applied to different data sets. By understanding the syntax and usage of the AVERAGE function, you can create more advanced calculations and gain valuable insights from your data.