SUMMARIZECOLUMNS – grouping in data model (DAX – Power Pivot, Power BI)

This is about the SUMMARIZECOLUMNS function. It groups values in a table, similarly to older SUMMARIZE. (More about it here.)

One group and one calculated column

Simple example. In this table we are going to group by Animals and summarize total numbers of units.

Let´s do it with this syntax:

  • Total units by Animals =
       SUMMARIZECOLUMNS(
          ‘Table'[Animal],
          “Total units”,
          SUM(‘Table'[Price per unit])
       )

There are these arguments – column used for grouping, name of new calculated group and calculation used to create it.

Multiple columns

This is an example of more complex syntax – with two grouping columns and two calculated columns, using for example SUMX function.

Total prices by Animals and Colors =
  • SUMMARIZECOLUMNS(
       ‘Table'[Animal],
       ‘Table'[Color],
       “Total units”,
       SUM(‘Table'[Units]),
       “Total price”,
       SUMX(
          ‘Table’,
          ‘Table'[Price per unit]*’Table'[Units])
       )

Now there are two grouping columns, two name columns and two calculated columns.