Skip to content

DAX AVERAGEX Function in Power BI

    DAX AVERAGEX Function in Power BI

    Last Updated on: 30th June 2024, 06:09 pm

    In the realm of data analysis and business intelligence, Power BI stands out as a powerful tool for transforming raw data into actionable insights. At the core of Power BI’s analytical capabilities lies DAX (Data Analysis Expressions), a formula language that enables complex calculations and data manipulations. Among the many functions in DAX, AVERAGEX emerges as a versatile tool for calculating averages based on complex expressions.

    Understanding AVERAGEX: Beyond Simple Averages

    Unlike the straightforward AVERAGE function, which calculates the arithmetic mean of a column, AVERAGEX allows you to evaluate an expression for each row in a table and then calculate the average of those results. This capability opens up a world of possibilities for intricate calculations and data modeling.

    The AVERAGEX Syntax

    The basic syntax of the AVERAGEX function is:

    AVERAGEX(<table>, <expression>)


    • <table> is the table containing the rows for which the expression will be evaluated.
    • <expression> is the expression to evaluate for each row in the table.

    Now, let’s dive into various examples to illustrate the power and flexibility of AVERAGEX.

    12 Practical Examples of AVERAGEX in Action

    1. Basic AVERAGEX: Calculating Average Sale Price

    Consider a simple Sales table:


    To calculate the average sale price, we can use:

    AVERAGEX(Sales, Sales[Price])

    This formula calculates the average of the Price column: (5 + 7 + 12) / 3 = 8.

    2. Weighted Average with AVERAGEX

    Using the same Sales table, let’s calculate the weighted average price based on quantity:

    AVERAGEX(Sales, Sales[Price] * Sales[Quantity]) / SUM(Sales[Quantity])

    This formula multiplies Price by Quantity for each row, sums the results, and then divides by the total quantity:
    ((510) + (715) + (12*8)) / (10 + 15 + 8) = 251 / 33 ≈ 7.61.

    When dealing with multiple related tables, AVERAGEX can be combined with the RELATED function. Consider two tables:





    To calculate the average sale price, we can use:

    AVERAGEX(Sales, RELATED(Products[Price]))

    This formula retrieves the Price from the Products table for each row in the Sales table and calculates the average: (5 + 7 + 12) / 3 = 8.

    4. Conditional AVERAGEX: Applying Logic to Calculations

    AVERAGEX can incorporate conditional logic using IF statements:

    AVERAGEX(Sales, IF(Sales[Quantity] > 10, Sales[Price], BLANK()))

    This formula only considers prices for products with a quantity greater than 10. The result would be 7, as only Product B meets the condition.

    5. AVERAGEX with Variables: Enhancing Readability

    Using variables can make complex AVERAGEX formulas more readable:

        VAR TotalSale = Sales[Quantity] * Sales[Price]
        RETURN TotalSale

    This formula calculates the average total sale amount: ((105) + (157) + (8*12)) / 3 ≈ 83.67.

    6. Nested AVERAGEX: Handling Complex Scenarios

    AVERAGEX can handle nested calculations, such as applying discounts:

        Sales[Price] * (1 - AVERAGEX(Discounts, Discounts[DiscountPercentage]))

    Assuming a Discounts table with discount percentages, this formula calculates the average price after applying the average discount.

    7. AVERAGEX with Date Calculations: Time-Based Analysis

    AVERAGEX can incorporate date-based filtering:

        FILTER(Sales, Sales[Date] >= DATE(2022, 01, 01) && Sales[Date] < DATE(2023, 01, 01)),

    This formula calculates the average price for sales in the year 2022.

    8. AVERAGEX with SUMX and COUNTX: Custom Average Calculations

    AVERAGEX can be replicated using SUMX and COUNTX for more control:

    SUMX(Sales, Sales[Price] * Sales[Quantity]) / COUNTX(Sales, Sales[Price])

    This formula calculates a custom average that weights prices by quantity.

    9. AVERAGEX with FILTER: Focused Averages

    AVERAGEX can be combined with FILTER for more specific calculations:

        FILTER(Sales, Sales[Product] = "A"),

    This formula calculates the average price only for Product A.

    10. AVERAGEX with ALL: Comparing to Overall Average

    AVERAGEX can be used to compare specific averages to overall averages:

    AVERAGEX(Sales, Sales[Price]) - AVERAGEX(ALL(Sales), Sales[Price])

    This formula calculates the difference between the current average price and the overall average price across all sales.

    11. AVERAGEX with EARLIER: Running Averages

    AVERAGEX can be used to calculate running averages:

        FILTER(Sales, Sales[Date] <= EARLIER(Sales[Date])),

    This formula calculates a running average of prices up to each date in the Sales table.

    12. AVERAGEX with SWITCH: Multi-Condition Averages

    AVERAGEX can handle multiple conditions using SWITCH:

            Sales[Quantity] <= 5, Sales[Price] * 1.1,
            Sales[Quantity] <= 10, Sales[Price] * 1.05,

    This formula calculates the average price with a 10% markup for quantities 5 or less, a 5% markup for quantities between 6 and 10, and no markup for larger quantities.

    Harnessing the Power of AVERAGEX

    The AVERAGEX function in DAX is a versatile tool that allows for complex average calculations in Power BI. By understanding and mastering AVERAGEX, data analysts and business intelligence professionals can unlock new levels of insight from their data.

    From basic averages to complex conditional logic and multi-table operations, AVERAGEX provides the flexibility needed to tackle a wide range of analytical challenges. As you continue to work with Power BI and DAX, incorporating AVERAGEX into your formulas will undoubtedly enhance your ability to extract meaningful insights from your data.

    Remember, the key to mastering AVERAGEX lies in practice and experimentation. Try modifying these examples to fit your specific data scenarios, and you’ll soon find yourself creating powerful, insightful calculations with ease. Whether you’re analyzing sales data, financial metrics, or any other numerical datasets, AVERAGEX will prove to be an invaluable tool in your DAX arsenal.

    Share this post on social!

    Comment on Post

    Your email address will not be published. Required fields are marked *