Downloads
DAXTable constructionIntermediate

SUMMARIZECOLUMNS

Groups a table by the columns you name, calculates measures for each group, and drops groups where every measure is blank.

Syntax
SUMMARIZECOLUMNS(<groupBy_columnName> [, <groupBy_columnName>] ... [, <filterTable>] ... [, "<name>", <expression>] ...)

Returns: A table.

What it does

SUMMARIZECOLUMNS is the function Power BI uses behind most visuals. You list the columns to group by, the measures to calculate, and any filters, and it returns one row per group that has data. It is usually faster than building the same table with SUMMARIZE and ADDCOLUMNS. It also drops groups where every measure is blank. Use it in a DAX query (EVALUATE) to check numbers or to export a report table.

Syntax and arguments

Example

Gross margin and margin percent by product category and year, as a DAX query.

EVALUATE
SUMMARIZECOLUMNS (
    Product[ProductCategory],
    'Date'[Year],
    "Gross Margin", [Gross Margin],
    "Margin %", [Gross Margin %]
)

Each row is a category and year that has sales. The numbers come from the same measures the reports use, so they match the dashboard.

Example: a second use

The same kind of report, limited to the online channel and grouped by country.

EVALUATE
SUMMARIZECOLUMNS (
    Stores[Country],
    FILTER ( ALL ( Channel[ChannelName] ), Channel[ChannelName] = "Online" ),
    "Online Sales", [Total Sales]
)

The filter argument is a table of the online channel. Each row is a country with its online sales. Countries with no online sales are dropped.

Things to watch

In business terms

Exporting a management table for a variance pack, testing a measure before it goes into a dashboard, and building a summary to check against the ledger. It is the function to reach for when a report must show exactly the groups that have activity.

Related functions

All DAX functions · View as Markdown · DAX fundamentals