Dax Function: AVERAGEX

Category: Aggregation functions

The AVERAGEX function in Power BI is a DAX (Data Analysis Expressions) function used to calculate the average (arithmetic mean) of an expression evaluated over a table or set of rows.

Purpose:

  • To compute averages dynamically based on custom expressions, not just static column values.
  • It evaluates each row in a specified table or subset of data, computes the expression for each row, and then averages the resulting values.

Type of Calculations:

  • Row-by-row evaluation of a DAX expression.
  • Aggregation of complex computed values into an average.

Practical Use Cases:

  1. Custom Metrics: Compute averages for derived metrics such as profit margins, sales growth percentages, or weighted values.
  2. Grouped Analysis: Analyze data at a group level by applying filters dynamically.
  3. Advanced Aggregations: Use with calculated measures that depend on complex logic.

AVERAGEX(<table>, <expression>)</expression></table>
ParameterTypeDescription
tableTableThe table or set of rows over which the expression is evaluated.
expressionExpressionA DAX expression evaluated for each row of the table to compute the average.

How Does AVERAGEX Dax Function Works?

  1. Row-by-Row Evaluation:
    • The expression is calculated for each row in the specified table.
  2. Aggregate Results:
    • The results of the expression are summed and divided by the number of rows in the table (excluding rows with BLANK() results for the expression).
  3. Dynamic Calculation:
    • Since the table can be filtered dynamically, the function adapts to context changes.

For example, if we use:

AVERAGEX(Sales, Sales[Revenue] / Sales[UnitsSold])

 

  • For each row, it calculates Sales[Revenue] / Sales[UnitsSold].
  • Averages the results of all rows.


What Does It Return?

The AVERAGEX function returns a numeric scalar value representing the arithmetic mean of the values produced by evaluating the expression for each row in the table.

When Should We Use It?

  • Custom Averages: Calculate averages for derived columns or expressions that aren’t available as raw columns.
  • Filtered Data: Analyze averages on subsets of data dynamically adjusted by filters.
  • Advanced Calculations: Use in scenarios involving weighted averages, complex logic, or combinations of multiple columns.

Examples

Basic Usage

Compute the average price per unit sold:


AvgPricePerUnit = AVERAGEX(Sales, Sales[Revenue] / Sales[UnitsSold])

Output: The average price is calculated based on the revenue-to-units ratio for each row.

Column Usage

Evaluate the average of a column with filters applied:


AvgFiltered = AVERAGEX(FILTER(Sales, Sales[Region] = "North"), Sales[Revenue])

Calculates the average revenue for rows in the “North” region.

Advanced Usage

Combine with other DAX functions for weighted averages:


WeightedAvg =
AVERAGEX(
Sales,
(Sales[Revenue] * Sales[Weight]) / SUM(Sales[Weight])
)

Result:Computes a weighted average revenue based on the weight column.

Tips and Tricks

  • Optimize for Filters: Use with CALCULATE or FILTER for advanced control over the rows being averaged.
  • Avoid Division Errors: Ensure the denominator in expressions is not zero to prevent errors.
  • Performance Considerations: For large tables, minimize the complexity of the expression to enhance performance.
  • Check for Blanks: Rows where the expression returns BLANK() are excluded from the average calculation.

Performance Impact of AVERAGEX DAX Function:

  • Row-by-Row Calculation: Each row’s expression is evaluated individually, so performance may degrade for large datasets with complex expressions.
  • Optimize with Filters: Apply pre-filters to reduce the number of rows processed.

Related Functions You Might Need

  • AVERAGE: Averages numeric column values without custom row-level expressions.
  • SUMX: Similar to AVERAGEX, but computes the sum of an expression over a table.
  • CALCULATE: Filters and modifies context for advanced aggregation with AVERAGEX.
  • MINX/MAXX: Finds the minimum or maximum value of an expression evaluated over a table.

Want to Learn More?
For more information, check out the official Microsoft documentation for AVERAGEX. You can also experiment with this function in your Power BI reports to explore its capabilities.

If you’re looking to unlock the full potential of Power BI and take your data insights to the next level, our expert Power BI consulting services are here to help. Whether you need assistance with implementing advanced DAX functions like the ones discussed here, creating interactive dashboards, or optimizing your data models for better performance, our team of seasoned Power BI consultants is ready to provide tailored solutions for your business. Visit our Power BI Consulting page to learn more about how we can empower your organization with data-driven decisions.

1. What is the difference between AVERAGE and AVERAGEX in Power BI?

AVERAGE computes the mean of a column’s numeric values, while AVERAGEX calculates the mean of a DAX expression evaluated row by row.

2. Can AVERAGEX handle filtered data?

Yes, you can use CALCULATE or FILTER with AVERAGEX to apply filters dynamically.

3. Does AVERAGEX exclude blank values?

Yes, rows where the expression evaluates to BLANK() are excluded from the calculation.

4. Is AVERAGEX suitable for large datasets?

It can handle large datasets, but optimizing the expression and applying filters can improve performance.

5. Can AVERAGEX compute weighted averages?

Yes, by using a formula with weighted values in the expression.

Previous article
Dax Function: AVERAGEA
Next article
Dax Function: COUNT