# SUMMARIZE Function in DAX

## Simplest usage

In its simplest form SUMMARIZE can be used to get distinct values from several columns. We have sample data in the table named SampleTable (1).

SUMMARIZE can work with columns from related tables. We added table (1) with unique values for Representatives, and we made a relation (2) between that table and original “Rep” column. Now, we can still use SUMMARIZE function that will use this new RepDetached column. Note, that this will only work for 1:N relations, where table mentioned in SUMMARIZE function is on “N” side of relation.

## SUMMARIZE With Aggregations

By using aggregations, we can add summary columns in the final table. We can aggregate values from current table and from related table. We can give new names to those columns.

``````EVALUATE
SUMMARIZE( SampleTable, [Region], "SumOfUnits", SUM( SampleTable[Units] )
, "Representatives", DISTINCTCOUNT( RepDetached[RepDetached] ) )
ORDER BY [Region]``````

## ROLLUP

Rollup is helper function that will provide us Grand Total and all other subtotals for our hierarchy.

## ROLLUPGROUP

ROLLUPGROUP is another helper function. It is used in combination with ROLLUP function. Purpose of ROLLUPGROUP function is to exclude subtotals for some columns from the final result of SUMMARIZE.

## ISSUBTOTAL

ISSUBTOTAL is a function that informs whether current row is a subtotal row for some column. This function receives an argument that is one of the columns included in the ROLLUP function. If that column is aggregated by subtotal in the current row, then ISSUBTOTAL function will return TRUE in that row.

## Filter and Row Context

SUMMARIZE can create new columns in row context. Only the columns used for grouping can be used (Region and Item).

## Nested grouping

We can first group our data by columns “Region” and “Item” (1). Then, we want to group table (1) by “Region” column (2).

The easiest way to solve this problem is to use another grouping function, “GROUPBY”, as outer function. This function will accept “SumOfUnits” column name without table quailifier.

``````EVALUATE
VAR SummarizeFunction = SUMMARIZE( SampleTable, [Region], [Item]
, "SumOfUnits", SUM( SampleTable[Units] )    )
VAR GroupByFunction = GROUPBY( SummarizeFunction, [Region]
, "SumOfUnits2", SUMX( CURRENTGROUP(), [SumOfUnits] )   )
RETURN GroupByFunction ``````

If we do not use aggregated column in SUMMARIZE function, then nesting is possible:

``````EVALUATE
SUMMARIZE(
SUMMARIZE( SampleTable, [Region], [Item] )
, [Region]
) ``````

## How does SUMMARIZE function work?

Most of us know how advanced filter in Excel works.

This “Criteria table” is how filter context is defined in Power BI. Filter context is a table. What filter context table do we expect is used, when aggregate values are calculated in SUMMARIZE function? We expect that filter context table has only columns that are used for grouping. We could graphically represent this like:

But, this is not how SUMMARIZE filter works. On our surprise, this is how SUMMARIZE really creates its filter. SUMMARIZE will first filter original SampleTable with region “Central” and item “Pen Set” (1). After this, whole table segment (2) will be used as a filter context.

The reason why SUMMARIZE works like this is because SUMMARIZE has full ability to control filter context of aggregated values with CALCULATE function. We can disable or modify some of the filters.

## Example

Let’s assume that we can not sell “Desk” item in “East” region and we can not sell “Pen Set” item in “West” region because of the government. We want to know what is the share of each Region in all the units sold, but only for items that are allowed for that Region. We can accomplish this by using only one function – SUMMARIZE.

In the image bellow, we can see that “Central” region is selling all of the 5 items (column “B”). That is why we compare its sale with 2.121 units (column “D”) which is total number of units for all the regions. For “East” and “West” this number is 2.111 and 1.726 because those totals are corrected not to include items “East” and “West” region are not allowed to sell.

``````EVALUATE
SUMMARIZE( SampleTable
, [Region]
, "Distinct items", DISTINCTCOUNT( SampleTable[Item] )
, "Units Total for Region", SUM( SampleTable[Units] )
, "Total for Distinct Items"
, CALCULATE( SUM( SampleTable[Units] ), ALLEXCEPT( SampleTable, SampleTable[Item] ) )
, "Ratio", SUM( SampleTable[Units] )
/ CALCULATE( SUM( SampleTable[Units] ), ALLEXCEPT( SampleTable, SampleTable[Item] ) )
)
ORDER BY [Region]``````

In our formula, blue part is removing all filters except filter for items, when calculating Total sale. So, we don’t filter “Region” column in our filter context. In the image bellow we can see how this influence filter context for “West” region. For “West” region filter context is the whole SampleTable with rows that contains “Pen Set” item removed (1). It is similar for “East” (2). For “Central” we don’t filter SampleTable at all (3). That is why column “D” has the biggest value for “Central” region and smaller values for “West” and “East” region.

## Performance Consequence

Because of the complexity of how SUMMARIZE function creates its filter context, performance of this function will not be impressive. We should use this function only when we need distinct rows for several columns (simplest case) or when we want to modify filter context (the most complex case).