SUMMARIZECOLUMNS in DAX: Group Data and Calculate Aggregated Values

What Is SUMMARIZECOLUMNS in DAX?

SUMMARIZECOLUMNS is a DAX table function used to create a summarized
table by grouping data using one or more columns and calculating expressions such as
SUM, AVERAGE, COUNT, and other measures or expressions.

It is especially useful when writing DAX queries where you want to see an aggregated
result for each category, customer, region, product, or another grouping column.

1. Grouping Product Category and Calculating Total Sales

Query

EVALUATE
	SUMMARIZECOLUMNS(
		'Product'[Category],
		"Total Sales", SUM(Sales[Sales Amount])
	)

Step-by-step explanation

Step 1: EVALUATE
EVALUATE tells DAX to return the result of the table expression.
Since SUMMARIZECOLUMNS returns a table, it can be used directly after EVALUATE.

Step 2: SUMMARIZECOLUMNS
SUMMARIZECOLUMNS creates the summarized result table.
In this query, we are grouping the sales data by product category.

Step 3: ‘Product'[Category]
This is the grouping column. DAX creates one result row for each
category represented in the summarized result.

Step 4: “Total Sales”
This is the name assigned to the calculated output column.

Step 5: SUM(Sales[Sales Amount])
The SUM function adds the values from
Sales[Sales Amount] for each category.

What will you get?

The query returns a summarized table containing the product category and the
corresponding total sales.

The category names and sales values above are illustrative. Your actual result
will depend on the data available in your model.

2. Applying a Filter with SUMMARIZECOLUMNS

Query

EVALUATE
	SUMMARIZECOLUMNS(
		'Product'[Category],
		FILTER('Product', 'Product'[Color] = "Red"),
		"Total Sales", SUM(Sales[Sales Amount])
	)

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the table generated by
SUMMARIZECOLUMNS.

Step 2: ‘Product'[Category]
The result is grouped by Product[Category], so the output is organized
category by category.

Step 3: FILTER(‘Product’, ‘Product'[Color] = “Red”)
The FILTER expression filters the Product table so that
only products whose Color is “Red” are included in the
calculation context.

Step 4: “Total Sales”
This defines the name of the calculated output column.

Step 5: SUM(Sales[Sales Amount])
DAX calculates the sum of Sales[Sales Amount] for the summarized
category results while the Red product filter is applied.

What will you get?

The result is a category-level sales summary based on the products that satisfy
the filter condition Product[Color] = “Red”.

The values above are illustrative. The actual result depends on the relationships
and data in your model.

Understanding the Structure of SUMMARIZECOLUMNS

The two queries demonstrate the main building blocks of
SUMMARIZECOLUMNS:

SUMMARIZECOLUMNS(
    Grouping Column,
    Filter Expression,
    "Calculated Column", Expression
)

The filter expression is optional. Therefore, the first query uses the grouping column
followed directly by the calculated expression, while the second query adds a filter
between them.

Comparing the Two Queries

Important Notes

1. SUMMARIZECOLUMNS returns a table

SUMMARIZECOLUMNS produces a table containing the grouping columns
and the calculated expressions specified in the query.

2. Grouping determines the level of the result

In these examples, ‘Product'[Category] determines that the result
should be summarized at the product-category level.

3. Filters can be added to the query

The second query demonstrates how a FILTER expression can be included
to restrict the data before calculating Total Sales.

4. Calculated columns can be given custom names

The expression “Total Sales”, SUM(Sales[Sales Amount]) creates
an output column named Total Sales.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist