SUMMARIZECOLUMNS function returns a summary table over a set of groups. This new function is included in SQL Server 2016 Analysis Services (SSAS), Power Pivot in Excel 2016, and Power BI Desktop. SUMMARIZECOLUMNS, which is a replacement of SUMMARIZE and does not require the use of ADDCOLUMNS to obtain good performance
Power BI
SUMMARIZECOLUMNS function is very useful in Power BI report, especially to produce summarize/group/aggregate tables.
For example, if you need to create a summary table for sales value by subcategory, a year with some filters.
Summary Product Subcategory = SUMMARIZECOLUMNS
(
'Product Category'[Category],
'Product Subcategory'[Subcategory],
'Date'[Calendar Year],
FILTER('Product Category','Product Category'[Category]="Audio"),
"Total Sales",'Sales'[Sales Amount]
)
SSAS Tabular
You can use the function in SSAS cubes in similar ways, but you need to be aware how to use the syntaxes.
The following examples will give a clear idea about how to use SUMMARIZECOLUMNS function.
EVALUATE
SUMMARIZECOLUMNS
(
'Date'[Calendar Year],
"Total Sales",'Internet Sales'[Internet Total Sales]
)
EVALUATE
SUMMARIZECOLUMNS
(
'Product Category'[Product Category Name],
'Date'[Calendar Year],
"Total Sales",'Internet Sales'[Internet Total Sales]
)
EVALUATE
SUMMARIZECOLUMNS
(
'Product Category'[Product Category Name],
'Date'[Calendar Year],
FILTER('Date','Date'[Calendar Year]=2014),
"Total Sales",'Internet Sales'[Internet Total Sales],
"Total Units",'Internet Sales'[Internet Total Units]
)
EVALUATE
SUMMARIZECOLUMNS
(
'Product Category'[Product Category Name],
'Date'[Calendar Year],
FILTER('Date','Date'[Calendar Year]=2014),
"Total Sales",'Internet Sales'[Internet Total Sales],
"Total Sales with Correction",'Internet Sales'[Internet Total Sales]*0.99,
"Total Units",'Internet Sales'[Internet Total Units]
)
EVALUATE
SUMMARIZECOLUMNS
(
'Product Subcategory'[Product Subcategory Name],
'Date'[Calendar Year],
FILTER('Product Category','Product Category'[Product Category Name]="Bikes"),
"Total Sales",'Internet Sales'[Internet Total Sales],
"Total Units",'Internet Sales'[Internet Total Units]
)
Cheers!
Uma
