Dax Function: SUMMARIZE

Category: Table Manipulation Functions

The SUMMARIZE function in Power BI is a powerful DAX (Data Analysis Expressions) function used to group data in a table based on specified columns and optionally perform aggregations or create calculated columns.

Purpose

  • Grouping Data: Summarizes data by creating a new table grouped by one or more columns.

  • Custom Aggregations: Adds new columns with custom calculated values based on the group.

  • Simplified Reporting: Creates a table that consolidates information for easy analysis and visualization.

Type of Calculations

  • Performs group-by operations to create summarized tables.

  • Includes optional calculated columns for aggregated values.

Practical Use Cases

  1. Data Consolidation: Prepare grouped data for visualization or downstream calculations.

  2. Custom Aggregation Tables: Create tables summarizing sales, revenue, or other metrics.

  3. Exporting Aggregated Data: Generate summarized tables for external reporting.


SUMMARIZE (
<table>,
<groupby_columnname1>, <groupby_columnname2>, ...,
[Name1], [Expression1], [Name2], [Expression2], ...
)</groupby_columnname2></groupby_columnname1></table>
ParameterTypeDescription
TableTableThe table to summarize.
GroupBy_ColumnNameColumnOne or more columns to group the data by.
NameStringName of the new calculated column (optional).
ExpressionDAX ExpressionExpression to calculate values for the new column (optional).

How Does SUMMARIZE Dax Works

The SUMMARIZE function creates a new table by:

  1. Grouping rows in the input table based on specified columns.

  2. Optionally applying DAX expressions to calculate additional columns for each group.

Logical Principle

If given the input table:

Region | Product | Sales
  North  |   A    | 100 
North  |   B    | 200  
 South  |   A    | 150  

Using SUMMARIZE:

SUMMARIZE ( Sales, Sales[Region], "Total Sales", SUM(Sales[Sales]) )

Result:

Region | Total Sales
North  |    300       
South  |    150       

What Does It Return?

  • Type: Table.

  • Content: A new summarized table grouped by the specified columns, optionally including calculated columns.

When Should We Use It?

  • When you need to create a grouped summary of a table.

  • To calculate metrics like totals, averages, or counts for grouped data.

  • To prepare summarized data for export or further analysis.

Examples

Basic Usage :

Group Data Without Aggregations:


SUMMARIZE (
Sales,
Sales[Region],
Sales[Product]
)

Result:

Region | Product
——-   |——–
North   |    A
North   |    B
South   |    A

Column Usage

Group and Add Aggregated Column:


SUMMARIZE (
Sales,
Sales[Region],
"Total Sales", SUM(Sales[Sales])
)

Result:

Region | Total Sales
 North  |     300   
 South  |     150   
South  |       2   

Advanced Usage

Combine with FILTER:


SUMMARIZE (
FILTER ( Sales, Sales[Sales] &gt; 100 ),
Sales[Region],
"Total Sales", SUM(Sales[Sales])
)

Region | Total Sales
——-   |————
North   |     200
South   |      150

Tips and Tricks

  • Use SUMMARIZECOLUMNS for more optimized and performant summaries.

  • Combine with CALCULATE for conditional aggregations.

  • Avoid using SUMMARIZE in measures; instead, use it in calculated tables.

  • Be cautious of implicit relationships when grouping data.

Performance Impact of SUMMARIZE DAX Function:

  • Use SUMMARIZECOLUMNS for larger datasets or complex scenarios to improve performance.

  • Ensure grouping columns are properly indexed to minimize query execution time.

Related Functions You Might Need

FunctionDescription
SUMMARIZECOLUMNSA more optimized version of SUMMARIZE for grouped summaries.
GROUPBYSimilar to SUMMARIZE but requires explicit aggregations.
ADDCOLUMNSAdds calculated columns to an existing table.
SUMComputes the sum of a column.
AVERAGEComputes the average of a column.

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

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What is the SUMMARIZE function in Power BI?

The SUMMARIZE function groups data in a table based on specified columns and optionally adds aggregated columns.

2. How does SUMMARIZE differ from SUMMARIZECOLUMNS?

SUMMARIZECOLUMNS is a more efficient alternative with better performance for larger datasets.

3. Can SUMMARIZE create multiple calculated columns?

Yes, you can add multiple calculated columns by specifying their names and expressions.

No, it is typically used for calculated tables, not measures.

5. Can I use SUMMARIZE with filtered data?

Yes, combine it with FILTER to summarize only specific subsets of data.

Previous article
Dax Function: SUBSTITUTEWITHINDEX
Next article
Dax Function: SUMMARIZECOLUMNS