What is the Power BI AverageX function?
The Power BI AverageX is an aggregation function that calculates the arithmetic mean or average of a set of expressions that is evaluated over a table or table expressions. The Power BI AverageX is a powerful tool to perform average calculations dynamically in the context of a filtered expression. The Power BI AverageX function performs the expression evaluation for each row of the table. Post-evaluation, it calculates the average on the resulting set of evaluated expressions.
Table of contents
Key Takeaways
- The Power BI AverageX function calculates the arithmetic mean or simply put average of a set of expressions that is evaluated over a table or table expressions.
- It performs the expression evaluation for each row of the table and then calculates the average on the resulting set of evaluated expressions returning a decimal value
- You can use the Power BI AverageX function in the DAX expression to create a calculated column or measure.
- While AVERAGEX is a powerful function in the context of a dynamic expression, it has certain limitations that you must be aware of before recommending its use
Syntax
The syntax for the Power BI AverageX function is highlighted below:
AVERAGEX(<table>,<expression>)
This function accepts two input parameters that include:
- Table: It refers to a table or a table expression over which Power BI can perform aggregations
- expression: It is an expression that gets evaluated for each record of the table parameter (1st argument)
The Power BI AverageX function returns a decimal number.
In the next section, we will see how you can use the Power BI AverageX function with a step-by-step instructions guide.
How to use the Power BI AverageX function?
To use Power BI AverageX, you can follow the below step-by-step instructions:
Step 1: Launch the Power BI Desktop on your system.
Step 2: On the Home tab, use the Get data option to choose the dataset. Import the dataset using the system navigator and load it into Power BI by clicking on the Load button.
Step 3: Navigate to the Modeling tab and click on the New column option.
Step 4: In the formula bar prompt, specify the logic for the calculated column by using the DAX expression involving the Power BI AverageX function. Once the changes are done, either hit the Enter key or click on the Commit icon on the Power BI screen to save your changes.
Step 5: Navigate to the Visualizations pane to select a visual icon. Drag and drop the field attributes, including the newly created column, to the visual properties.
Step 6: Format the visual as per your requirements by navigating to the Format your visual tab. Apply customization such as Title, Alignment, Text, and Color to your visual. Your visual is now ready to be viewed in the report canvas.
Note: You can create measures using Power BI Averagex with filter. It will calculate the average on the resulting dataset post-filter. For example, the measure below, created using Power BI Averagex with a filter, would calculate the average sales per region for the last year.
Average Sales per Region = AVERAGEX(
‘Sales'[Region],
‘Sales'[Order Date] >= TODAY() – 365, // Filter for last 1 year
[Gross Sales]
)
The Power BI Averagex all rows may not be considered if there are any rows with blank or non-numeric values. Ensure that you have handled the blank values or 0 values (especially if you are dividing to avoid any divide-by-zero case).
There may be a few cases of Power BI Averagex not working. You can follow troubleshooting or ensure the syntax you have used is accurate. At times, data type incompatibility or incorrect column names may cause this issue.
Examples
In this section, we will demonstrate different scenarios where you can use the Power BI AverageX function.
Example #1 – Using AVERAGEX For Average Sales Per Month
In this example, we will showcase how you can use the Power BI AVERAGEX function to calculate the Average Sales Per Month. To demonstrate this example, we will use the Pie Sales dataset. The Pie Sales dataset contains the sales details of different flavor Pie along with their unit economics.
To calculate the Average Sales Per Month using the Power BI AVERAGEX function, follow the steps outlined below:
Step 1: Navigate to Home – Get data in Power BI Desktop. Select the dataset and import it into Power BI by clicking on the Load button.
Step 2: Navigate to the Modeling tab and select the New column option.
Step 3: In the formula bar prompt, specify the logic to calculate the revenue using the DAX expression.
Revenue = ‘Pie Day Dataset'[Sales Price] * ‘Pie Day Dataset'[Quantity]
Step 4: Again, navigate to the Modeling tab and select the New measure option.
Step 5: In the formula bar prompt, specify the logic to calculate the Average Sales Per Month using the DAX expression.
Here, we have calculated the monthly average sales by using the VALUES, AVERAGE, and AVERAGEX functions.
Average Sales Per Month = AVERAGEX(VALUES(‘Pie Day Dataset'[Order Date]),AVERAGE(‘Pie Day Dataset'[Revenue]))
Click on the Commit icon to save the changes.
Step 6: Navigate to the Visualizations pane and choose the Table visual. Drag and drop the fields from the Data pane to the Columns section of the visual.
Once the mapping is completed, you will see your visual in the report canvas as shown below:
Example #2 – Using AVERAGEX For Average Sales Per Customer
Let’s now look at creating another measure, i.e., Average Sales Per Customer, by using the Power BI AVERAGE function along with AVERAGEX. To demonstrate this example, we will use the American Furniture Sales dataset. The American Furniture Sales dataset contains the sales details of different furniture brands across the stores in the US.
We have outlined the step-by-step instructions on how to create the Average Sales Per Customer measure using the Power BI AVERAGEX function.
Step 1: In the Power BI Home tab, click on the Get data. From the left-hand navigator, select the datasets. Click on the Load button to load the data into the Power BI.
Step 2: Navigate to the Data pane. Right-click on the table and choose New measure from the context menu.
Step 3: Specify the business logic to create a new measure using the AVERAGEX function in the formula bar prompt.
Similar to 1st example, we have used VALUES, AVERAGE, and AVERAGEX functions to calculate the Sales Per Customer.
Average Sales Per Customer = AVERAGEX(VALUES(American_furniture_store[Customer Name]), AVERAGE(American_furniture_store[Sales]))
Hit on Enter to save your DAX expression changes for the measure.
Step 4: Navigate to the Visualizations pane. From the available list of visual options, select the Table visual icon. Map the data fields from the Data pane to the Columns section of the table.
Step 5: Navigate to the Visualizations pane. Select the Slicer visual icon. Map the data fields from the Data pane to the Field section of the Slicer.
Step 6: Navigate to Format your visual tab and apply formatting to your visual to make it visually appealing.
Finally, the formatted visual is available for your view in the report canvas, as shown below.
Example #3 – Using AVERAGEX For Average Sales Per Day
In this example, we will create another measure to calculate the average sales per day using the Power BI AVERAGEX function. Here we will use Grocery_Sales_dataset to demo this example. The Grocery_Sales_dataset contains the sales details of various grocery products, such as bread, milk, butter, etc., across different grocery stores in the US.
To calculate Average Sales Per Day using the Power BI AVERAGEX function, follow the instructions outlined below:
Step 1: Navigate the Power BI Home tab and use the Get data option to import your dataset into Power BI.
Step 2: Navigate to the Modeling tab, and select the New measure option.
You can also use the option highlighted in 2nd example to create a measure.
Step 3: In the formula bar prompt, write the DAX expression using the AVERAGEX function to create a calculated measure.
Average Sales Per Day = AVERAGEX(VALUES(Grocery_sales_dataset[sales_date]),AVERAGE(Grocery_sales_dataset[total_revenue]))
Save the changes by clicking on the Commit button.
Step 4: To create a visualization using the newly created measure, navigate to the Visualizations pane. Choose the Table visual icon from the list. Map the Columns section with the data fields from the Data pane including the newly created measure.
Step 5: Navigate to the report canvas. You will see the Table visual with mapped fields, as shown below.
Example #4 – Calculate the Average Revenue by Region
In this last example, we will demo the calculation of the Average Revenue by Region using the Power BI AVERAGEX function. Here, we have used the Global_Superstore_sales dataset. The Global_Superstore_sales dataset contains the dataset for global superstore sales across different segments, regions, categories, etc.
You may follow the steps highlighted below to create the measure to calculate the Average Revenue by Region:
Step 1: In the Power BI Home tab, select the Get data option. Click on the Load button to import your data into the Power BI data model.
Step 2: Navigate to the Data pane. Right-click on the Global_Superstore_Sales table, and select the New measure option from the context menu.
Step 3: Specify the DAX expression in the formula bar prompt using the Power BI AVERAGEX function to create a new measure to calculate the average sales per region.
Average Sales Per Region = AVERAGEX(VALUES(Global_Superstore_Sales[region]), AVERAGE(Global_Superstore_Sales[sales]))
Step 4: Navigate to the Visualizations pane and choose the Table visual icon. From the Data pane, drag and drop the fields to the Columns section.
This will create your Table visual in the report canvas as shown below
Important Things to Note
- The Power BI AverageX function mandates both the table and expression as arguments to evaluate an expression. Any non-numeric or null values in columns are not allowed.
- It returns a blank value if it evaluates no records to aggregate in the underlying table.
- Where you have a table with rows but don’t meet the specified input conditions, the Power BI AverageX function returns 0 value.
- This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Frequently Asked Questions (FAQs)
The AVERAGEX skips the blank and null values while performing the average calculation. It only considers numeric values for evaluating the resulting set from the expression.
Yes, you can use the AVERAGEX function with time-intelligence functions like DATESBETWEEN, PREVIOUSMONTH , or SAMEPERIODLASTYEAR, etc. Use of such function will enable you to calculate average values in the context of different time intervals.
AVERAGEX function as such does not directly support any conditional logic in Power BI. However, you can use the AVERAGEX function as part of a conditional logic or expression.
For example, using the AVERAGEX in combination with IF or SWITCH functions within the expression to apply different calculations based on conditional logic.
AVERAGEX does not handle errors or divide-by-zero scenarios natively in Power BI. You must ensure that any 0 values available in your dataset are properly handled through data checks or using any alternative value assignment to avoid any divide-by-zero conditions.
Recommended Articles
Guide to Power BI AverageX. Here we explain its syntax, how to use these function in Power BI, with examples & points to remember. You can learn more from the following articles –
Leave a Reply