SUMMARIZE in DAX: Group Data and Calculate Aggregations

What Is SUMMARIZE in DAX?

SUMMARIZE is a DAX table function used to create a summary table by grouping data based on one or more columns. It can also add calculated expressions, such as SUM, AVERAGE, COUNT, and other aggregations, to the grouped result.

The basic syntax is:

SUMMARIZE(
    <Table>,
    <GroupBy_Column>,
    <GroupBy_Column>,
    "Name", <Expression>
)

In our example, the Sales table is summarized using Product Category and Product Subcategory, while Total Sales and Total Tax are calculated for each group.

1. Retrieve the Sales Table

Before creating the summary, the query first retrieves the complete Sales table.

EVALUATE
    Sales

Step-by-step explanation

Step 1: EVALUATE
EVALUATE is used to return a table expression as the query result.

Step 2: Sales
Sales is the table expression being returned. Therefore, the complete Sales table is displayed.

2. Retrieve the Product Table

The next query retrieves the complete Product table.

EVALUATE
    'Product'

Step-by-step explanation

Step 1: EVALUATE
The EVALUATE statement returns the result of the table expression.

Step 2: ‘Product’
‘Product’ is the table being returned. The single quotes are commonly used when referencing a table name.

This query allows us to inspect the Product table, including the Category and Subcategory columns that will be used for grouping.

3. Create a Summary Using SUMMARIZE

Query

EVALUATE
    SUMMARIZE(
        Sales,
        'Product'[Category],
        'Product'[Subcategory],
        "Total Sales", SUM(Sales[Sales Amount]), 
        "Total Tax", SUM(Sales[Tax Amount])
    )

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the summary table generated by the SUMMARIZE function.

Step 2: SUMMARIZE
SUMMARIZE creates a new table by grouping the data according to the columns specified after the source table.

Step 3: Sales
Sales is the table from which the summary is created.

Step 4: ‘Product'[Category]
This column is used as the first grouping column. The result will contain one group for each relevant product category.

Step 5: ‘Product'[Subcategory]
This column is used as the second grouping column. The result is therefore grouped by the combination of Category and Subcategory.

Step 6: “Total Sales”
This is the name of the calculated output column.

Step 7: SUM(Sales[Sales Amount])
The SUM function calculates the total Sales Amount for each Category and Subcategory group.

Step 8: “Total Tax”
This is the name of another calculated output column.

Step 9: SUM(Sales[Tax Amount])
The SUM function calculates the total Tax Amount for each Category and Subcategory group.

What will you get?

The result is a summary table containing the grouping columns and the calculated totals.

The values above are illustrative. Your actual results depend on the data available in the Sales and Product tables.

Understanding the Structure of SUMMARIZE

The SUMMARIZE function can be understood as two main parts:

1. Grouping columns define how the rows should be grouped.
2. Calculated expressions define the metrics that should be returned for each group.

SUMMARIZE(
    Sales,
    'Product'[Category],
    'Product'[Subcategory],
    "Total Sales", SUM(Sales[Sales Amount]),
    "Total Tax", SUM(Sales[Tax Amount])
)

Category and Subcategory Grouping

When both Category and Subcategory are specified, DAX groups the data using their combination.

For example, a result conceptually looks like this:

Each unique combination of Category and Subcategory becomes a group, and the aggregation expressions are evaluated for that group.

Understanding the Calculated Columns

The expressions after the grouping columns create additional columns in the summary table.

"Total Sales", SUM(Sales[Sales Amount]),
"Total Tax", SUM(Sales[Tax Amount])

“Total Sales” is the name assigned to the first calculated column, while SUM(Sales[Sales Amount]) calculates its value.

Similarly, “Total Tax” is the name assigned to the second calculated column, while SUM(Sales[Tax Amount]) calculates its value.

Important Notes

1. SUMMARIZE returns a table

SUMMARIZE is a table function. Therefore, it can be used directly with EVALUATE in a DAX query.

2. Multiple grouping columns are supported

You can provide multiple columns to create a more detailed grouping, as demonstrated with Category and Subcategory.

3. Aggregations can be added to the summary

Functions such as SUM can be used to calculate metrics for each group. The expressions are evaluated against the corresponding grouped context.

4. The original tables are not modified

SUMMARIZE creates a table expression for the query result. It does not modify the original Sales or Product table.

EVALUATE
    SUMMARIZE(
        Sales,
        'Product'[Category],
        'Product'[Subcategory],
        "Total Sales", SUM(Sales[Sales Amount]),
        "Total Tax", SUM(Sales[Tax Amount]),
        "Average Sales", AVERAGE(Sales[Sales Amount])
    )

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist