SUMMARIZE – groupping in data models (DAX – Power Pivot, Power BI)

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.

9 Comments

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

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

  9. 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?

Comments are closed.