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.

8 Comments

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

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

Leave a Reply

Your email address will not be published.

*

clear formPost comment