This article is about SUMMARIZE function, which groups rows in data models. It¨s little similar to Pivot Tables in common Excel or to groupping in Power Query (Get and Transform). The result of SUMMARIZE is always a table. Which means it can be used in new table in data models, or, in combination with other function, for new measure or new column creation - so as it results in one number.

We will use this table with animals: If you want to see, for example, average ages and total counts by animal kinds, write this:

• Totals = SUMMARIZE('Table',
'Table'[Animal],
"Average age per kind",
AVERAGE('Table'[Age (years)]),
"Number of them",
COUNT('Table'[Age (years)]))

Because:

• Totals = SUMMARIZE('Table', - name of result table, function and name of source table
'Table'[Animal], - column used for grouping (you can have more of them)
"Average age per kind", - name of new column with calculation (header)
AVERAGE('Table'[Age (years)]), - calculation of new column
"Number of them", - name of another column with calculation
COUNT('Table'[Age (years)])) - calculation of another column

The result will be like this: You can simply use SUMMARIZE or GROUPBY to get the number of unique rows, based on more columns than 1.

1. Philip says:

explanation is very clear

2. Mary says:

This was exactly what I needed, thanks!

3. Matty-D says:

What if you need to join another table that contains the date?

4. Jiří Beran says:

To create a table containing just date you can use the Calendar function

5. Anonymous says:

your explanation is simple . thanks

What if you are summing based on the three categories? (ie, Goods, Goods Category, Weekday, Assistant)

7. MIndstine says:

it says syntax error on semicolon

8. Jiří Beran says:

Try to replace them by comma.

9. Dan Paul says:

Question… This makes total sense. My question is this. I have a table that I summarized using my Calendar Table and then layering in calculations from related tables.

Summarize(Calendar, BOM, EOM, WeekBeginning, WeekEnding, WeekNo. “TotalOfWidgetsTable1”, Calculated(Count(WidgetId),Fitler(Table1,WidgetCreatedDate>=WeekEnding)),
“TotalOfWidgetsTable2”, Calculated(Count(WidgetId),Fitler(Table2,WidgetCreatedDate>=Weekending))
)

This seems to calculate the total widgets for both tables for each week, but when I compare these totals to say a measure the calculates the same value, the values form the summary table are much lower. Curious as to why? Are they not essentially do the same thing?