This article is about the GROUPBY function. It creates groups or subtotals in DAX and works somehow similar to Pivot Tables.

We will use this table with cars, that can be grouped by various columns.

Simple grouping 

Let´s group the table by brands (which is the same as make a list of brands).

Create a new ,calculated table and define it by this command:

The syntax contains:

  • Name of table
  • Name of column used for grouping.

Grouping by multiple columns

But what if you needed to group the table not only by brands, but by models as well? You can add new argument referencing the other column:

Now the syntax contains:

  • Name of table
  • All columns used for grouping.

Grouping with calculations

Off course, we can add some sums, counts or something like this. In this example I´d like to see the sums and counts:

Now the syntax contains:

  • Name of table
  • All columns used for grouping
  • Name of first calculated column - using such functions as COUNTX, SUMX... combined with CURRENTGROUP
  • Name and calculation for other columns

And two additional notes:

  • There is a SUMMARIZE function, quite similar to GROUPBY
  • GROUPBY results in a table - so you cannot use it (separately) for measure or calculated column (which means you cannot use it in Power Pivot, without nesting in other function).

1 Comment

  1. Hi. Good Article. I’d like to group by a table like that. But I need to be able to filter the information after group by per date. Is there any way to do that?

Leave a Reply

Your email address will not be published.

*

clear formPost comment