Home » SUMMARIZECOLUMNS Functon DAX

SUMMARIZECOLUMNS Functon DAX

SUMMARIZECOLUMNS Function DAX




SUMMARIZECOLUMNS function is a Power Bi Table Manipulation function in DAX which returns a summary table over a set of groups.

DAX SYNTAX

SUMMARIZECOLUMNS( <groupBy_columnName> [, < groupBy_columnName >]…,
[<filterTable>]…[, <name>, <expression>]…)

groupBy_columnName is a fully qualified column reference (Table[Column]) to a base table for which the distinct values are included in the returned table. Each groupBy_columnName column is cross-joined (different tables) or auto-existed (same table) with the subsequent specified columns.

filterTable is a table expression which is added to the filter context of all columns specified as groupBy_columnName arguments. The values present in the filter table are used to filter before cross-join/auto-exist is performed.

name is a string representing the column name to use for the subsequent expression specified.

expression is any DAX expression that returns a single value (not a table).

You can pass any number of parameters to SUMMARIZECOLUMNS function. If the first parameter is a reference to a column, the SUMMARIZECOLUMNS function understands that this is a column you would like to group by.

Lets look at an example of SUMMARIZECOLUMNS function in Power Bi.

We have a Dataset named as RegionSales, which contains the sales done by regions in particular years.

Region     Year        Sales

North 2020 2450456
South 2020 3500485
East 2020 1204358
West 2020 1250567
North 2019 2450489
South 2019 4507898
East 2019 5680579
West 2019 7890989
North 2018 4508656
South 2018 6753243
East 2018 8573496
West 2018 1250457

 

Lets create a summary table table using a SUMMARIZECOLUMNS function named as SummaryTable, which returns a new table that contains a  total sales by regions.

SummaryTable =
SUMMARIZECOLUMNS (
    RegionSales[Region],
    "OverALL Sum Of Sales By Region"SUM ( RegionSales[Sales] )
)

 

Now once you commit the DAX, it returns a total sales by regions. You can also see a table named as summarytable  is created in fields Pane.

As table Summarytable is already having a grouped rows based on region so, it takes a less time in comparision to actual RegionSales on display data on visual that helps you optimizing your report performance.

You can use now summarytable to dispaly regions by sales data on visuals as shown below.

 

SUMMARIZECOLUMN Function with FILTER

You can also use FILTER with SUMMARIZECOLUMN function, In reference of above example suppose you want to exclude North region from summarytable.

Lets modify above DAX by adding a Filter function as shown below.

SummaryTable =
SUMMARIZECOLUMNS (
RegionSales[Region],
FILTER (
ALL ( RegionSales[Region] ),
NOT ( RegionSales[Region] ) IN { "North" }
),
"OverALL Sum Of Sales By Region", SUM ( RegionSales[Sales] )
)

 

Lets display the data on visual, and you can see that summarytable does not have North Region data now.

 

Recommended for you

Learn more on Power Bi DAX

Power Bi Page Navigation

Power Bi Dynamic Page Navigation 

Create Index column using a Power Query




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.