Karan Gulati (SSAS Maestro)

The following example adds roll-up rows to the Group-By columns of the SUMMARIZE function call.

EVALUATE SUMMARIZE( ‘Internet Sales’,
ROLLUP (‘Date'[Calendar Year],Product[Product Category Name]),
“Sales”, ([Internet Total Sales]),
“Tax”,([Internet Total Tax Amount]))

Red Box shows the sub-total of Categories further Brown Box shows Total of all Categories across all years.

SUMMARIZE function returns a summary table for the requested totals over a set of groups. Readers familiar with T-SQL SELECT statement, this is the equivalent of writing a query using the GROUP BY clause.

Syntax –

SUMMARIZE(<table>, <groupBy_columnName>[, <name>, <expression>]…)

Demo –

The Following example returns summary of the Internet Sales grouped around Country, Category & Calendar

EVALUATE SUMMARIZE( ‘Internet Sales’,
Geography[Country Region Name],
Product[Product Category Name],
‘Date'[Calendar Year],
“Internet Total Sales”,
‘Internet Sales'[Internet Total Sales])

Notice you do not need to define the relationships between tables when using SUMMARIZE because the relationship is defined in the model.

Output –

Currently I am getting expected output but output is not sorted for sorting I need to use another function – Order By

EVALUATE SUMMARIZE( ‘Internet Sales’,
Geography[Country Region Name],
Product[Product Category Name],
‘Date'[Calendar Year],
“Internet Total Sales”,
‘Internet Sales'[Internet Total Sales])
ORDER BY Geography[Country Region Name], Product[Product Category Name],’Date'[Calendar Year]

Further I can add measure to Order By –EVALUATE SUMMARIZE( ‘Internet Sales’,
Geography[Country Region Name],
Product[Product Category Name],
‘Date'[Calendar Year],
“Internet Total Sales”,
‘Internet Sales'[Internet Total Sales])
ORDER BY                                                                                                                                                                                         Geography[Country Region Name], Product[Product Category Name],’Date'[Calendar Year], ‘Internet Sales'[Internet Total Sales]