- SUMMARIZE is a very commonly used function
that performs two operations.
It scans a table and can do a groupby.
You specify grouping by columns.
And the grouping by column can include columns
in the same table or in any related table.
And it can also create new columns,
which are added to the output.
In this first example, I'm using SUMMARIZE
to scan the sales table, this is the starting table,
and I group it by product brand and date calendar year.
Both product and date are linked through sales
through a many-to-one relationship
and that is the reason why we can use these columns
in order to do the grouping by.
The result contains the brand and the calendar year
for only the brands and the year
that are referenced in the sales table.
You can also use SUMMARIZE to create new columns.
In this example, I'm summarizing sales by brand and by year
but then I'm adding two columns,
quantity and brand and year.
Whenever you specify a new column,
you need to provide the column name
and the expression that defines the column value.
What makes SUMMARIZE unique is that the expressions
of the newly created columns
are computed in both a row context and a filter context.
So SUMMARIZE creates a filter context
with the values of the column that you are grouping by
but it also creates a row context
that lets you evaluate the values
of the current row of this dataset.
Indeed, you can see that I'm computing here quantity,
using SUM of sales quantity
and the result is different for every row
because this is evaluated in the filter context
of the given brand and year.
At the same time, I can reference product brand
and calendar year, which are the two groupby columns
because SUMMARIZE also created a row context
that let me evaluate those two values.
The presence of both a row context and a filter context
makes the calculations and the output of CALCULATE
very hard to understand in case it is
not exactly what you would expect.
This is the reason why we strongly discourage people
from using the newly created columns in SUMMARIZE
and instead, split the grouping by with SUMMARIZE
and surround it with ADDCOLUMNS to create columns.
ADDCOLUMNS only creates a row context
and the debugging and the understanding
of the values returned by ADDCOLUMNS is much simpler.
Using SUMMARIZE, you can also compute subtotals.
In this example, by using the ROLLUP function,
I'm summarizing sales, I'm grouping by brand
and calendar year but the presence of ROLLUP
means that I'm also interested in the subtotals.
You can see that in the result,
I have the brand, the year exactly as before
but now I have also additional columns,
Contoso with a blank for the year,
which is the subtotal for Contoso in any year.
And I also have a final row that contains blanks everywhere
for the groupby columns and this is the grand total.
This is especially useful when with a single query,
you want to retrieve not only the values
of the individual cells in your report
but also the subtotals at any level.
Finally, another feature that is unique...
of SUMMARIZE is that you can use SUMMARIZE
to group by calculated columns on the fly.
Here you see that I'm using ADDCOLUMNS
to iterate over sales.
I'm computing the sales type column
as a newly created column that contains an expression,
IF quantity is greater than one, it's large,
otherwise, it's small.
And then I can use SUMMARIZE
to group by the sales type column.
The result, small and large, is the amount
of all the sales that have a quantity
greater than one or equal to one.