SUMMARIZECOLUMNS
Groups a table by the columns you name, calculates measures for each group, and drops groups where every measure is blank.
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
- groupBy column: the columns that form the rows. Columns from related tables are allowed.
- filterTable (optional): a Boolean filter or table filter on one column, which limits the rows.
- name and expression (optional pairs): measures or expressions. Each name is in quotes.
- A filter argument cannot refer to a measure. Use columns in filters and measures in the value pairs.
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
- Groups where every measure is blank are removed. A group with no sales does not appear. If you need every member listed, use
ADDCOLUMNSon a list of members. - Use
KEEPFILTERSin a filter argument when the filter must intersect with the user's selection rather than replace it. - Results come back in no set order. Add
ORDER BYin the query to sort. - For a visual, Power BI builds this query for you. Writing it yourself is useful for checking a figure.
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.