Understanding Power BI SUMX, AVERAGEX, VALUES, and SUMMARIZE

This article explains how Power BI developers can effectively use the functions  Power BI SUMX, `AVERAGEX`, `VALUES`, and `SUMMARIZE` to control how a measure is grouped and evaluated. The focus is not solely on the functions themselves, but rather on the analytical problems they help solve.

Default measures in Power BI follow the current filter context and visual grouping. While this is useful for simple totals, it can be limiting when calculations need to occur at one level while being displayed at another. The functions discussed in this article allow developers to build a table of groups, iterate over that table, and return stable results.

Problem Definition: Grouping Challenges in Power BI

Analysts often require measures that behave differently from the default totals in a visual. Common business questions include:

• What is the average monthly sales per customer, even when the report is sliced by product?
• What is the total of customer-level totals instead of the raw transaction total?
• What is the average of region-level results, rather than the average of every row in the fact table?

These scenarios represent grouping problems. The report may display Product, Region, or Date, but the calculation needs to occur at a different grain. This is where iterator functions and table-shaping functions become essential.

Key Concept: How to Use DAX Functions for Grouping

A useful way to think about these functions is as follows:

1. Build a table of the groups you want.
2. Evaluate an expression for each row in that table.
3. Return the sum or average of those evaluated results.

In practical terms:

• `VALUES` returns the distinct values of a column in the current filter context.
• `SUMMARIZE` returns a grouped table with one row per group.
• `SUMX` iterates over that table and sums the row-by-row results.
• `AVERAGEX` iterates over that table and returns the average of the row-by-row results.

These functions address a common analytical need by allowing aggregation over a virtual grouping table instead of relying solely on the visible rows in the visual.

Data Example: Sales Table Analysis

Assume we have a simple sales table structured as follows:

If we want customer totals, the correct result is:

Now, suppose the visual shows Product. The raw sales by product are:

If the business question is: “What is the average customer total sales across customers who bought each product?” then the calculation must first produce customer totals and then average those totals within the current product context. This is the type of problem these functions are designed to solve.

Tool Implementation: Using DAX Functions in Power BI

Power BI utilizes DAX to define measures. The relevant functions include:

• `SUMX(table, expression)`
• `AVERAGEX(table, expression)`
• `VALUES(column)`
• `SUMMARIZE(table, groupBy_columnName, [name], expression)`

The practical meaning of these functions is straightforward. `SUMX` and `AVERAGEX` are iterator functions that evaluate an expression for each row of a table and then combine the results. `VALUES` is often used to obtain the distinct list of categories currently in scope, while `SUMMARIZE` is used to create a grouped virtual table with one or more grouping columns.

Syntax Examples

• `SUMX(VALUES(Customer[Customer]), [Sales])`
• `AVERAGEX(VALUES(Customer[Customer]), [Sales])`
• `SUMMARIZE(Sales, Customer[Customer], “Customer Sales”, SUM(Sales[Sales]))`

Applied Example: Calculating Customer Total Sales

If the goal is to calculate customer total sales across the current filter context, a common measure is:

“`DAX
Customer Total Sales = SUMX(VALUES(Customer[Customer]), [Sales])
“`

If `[Sales]` is already a measure that returns sales in the current context, this pattern can be used to iterate over customers and combine their results.

For an average of customer totals:

“`DAX
Average Customer Sales = AVERAGEX(VALUES(Customer[Customer]), [Sales])
“`

Using the sample data, the result is:

Average Customer Total Calculation

The average customer total can be calculated as follows:

If the visual is filtered to the East region only, the customer list changes to A and C:

Average Customer Total in East Region

The average customer total in the East region can be calculated as follows:

This illustrates the importance of the pattern. The measure respects the filter context while also controlling the grain of the iteration.

Why This Feature Exists: Understanding Default Aggregation

Default aggregation in Power BI follows the current filter context and the shape of the visual. A simple measure such as:

“`DAX
SUM(Sales[Sales])
“`

returns the sum of visible rows in the current context. While this works for additive totals, it does not address questions like:

• Average of customer totals
• Sum of monthly averages
• Average of grouped values
• Nested aggregation across categories

The iterator pattern exists because there are times when you need to aggregate results that are already aggregated. This presents a different challenge than a straightforward column sum.

Important Behavioral Details: Understanding DAX Functions

There are several important behaviors to understand when using these functions:

• `VALUES` returns the distinct values currently in filter context. If the context is empty, it may return a blank row when referential integrity is broken and the model includes unmatched fact rows.
• `SUMMARIZE` creates a grouped table. While useful for virtual tables, it should be used carefully, as complex expressions inside `SUMMARIZE` can be harder to debug and may behave differently from a simple grouped table in calculations.
• `SUMX` and `AVERAGEX` iterate row by row. If the expression returns blank for some rows, `AVERAGEX` ignores those blanks in the average calculation.

These patterns are sensitive to filter context. A slicer on Product, Region, or Date changes the rows returned by `VALUES` or `SUMMARIZE`, meaning the measure can change even when the visual grouping remains the same. This behavior is usually expected but can be confusing if a developer assumes the iterator functions like a fixed table scan. Instead, it evaluates the current semantic model context.

For migration work, it is crucial to pay attention to row context and filter context. A naked column reference inside an iterator does not behave the same as a measure reference. Measures are evaluated in the current filter context for each iterated row.

Real Usage Patterns: Common Applications of DAX Functions

Common use cases for these functions include:

• Customer lifetime value
• Average order value by customer group
• Percent of total by grouped denominator
• Average of category totals
• Rolling summaries by month or quarter
• Ratio calculations with stable denominators
• Weighted averages
• De-duplicated totals

A frequent pattern is to use `VALUES` when only one grouping column is needed. Use `SUMMARIZE` when multiple grouping columns are required or when a grouped virtual table with named columns is necessary. In practice, developers often start with `VALUES` because it is simpler and easier to validate. `SUMMARIZE` is a better fit when the business question requires grouping by more than one field.

Conclusion

This article has explored the use of `SUMX`, `AVERAGEX`, `VALUES`, and `SUMMARIZE` in Power BI. By understanding how to leverage these functions, developers can effectively address complex analytical problems and create more meaningful reports.