GROUP, GROUPBY, and COUNTDISTINCT (+video)

For grouping, aggregating, filtering and sorting of your data, try the group, filter and sort properties associated with your component.

Introduction to GROUP and GROUPBY

Using GROUPBY with the Method parameter

GROUP, GROUPBY, COUNTDISTINCT

These functions are used to specify a series of unique elements to group together and remove duplicate values from your data. They are designed to complement each other to aggregate and simplify data.

  • GROUP allows you to remove duplicates from a column, such as when a column has multiple instances of the same value. 
  • GROUPBY collects values based on a specified aggregation method (like GROUP) so that the unique values align with a parallel column.
  • COUNTDISTINCT counts the number of each unique value in a column.

Note: The results for these functions are ordered alphabetically by list item.

You can download the data source used in this article here: Regions and units.

GROUP

In this example, the Regions in column A are grouped so duplicate Regions are not displayed.  

GROUP(SLICE(values))

values: set to the Regions data in column A (A:A), and wrapped in SLICE to remove the column header. 

GROUPBY

The Regions in column A are grouped and the Units in column B are grouped (summed unless specified otherwise) according to the Regions. 

GROUPBY(values1, values2)

  • values1: set to the Regions data in column A (A:A) and wrapped in SLICE to remove the column header. 
  • values2: set to the Units data in column B (B:B).

Optional steps

The default method is SUM(Values). You can set a different method by choosing one from the drop-down list or typing one in as a string.

GROUPBY(values1, values2,"method")

  • values1: set to the Regions data in column A (A:A).
  • values2: set to the Units data in column B (B:B).
  • method: set to "average(values)".

COUNTDISTINCT

COUNTDISTINCT counts the number of each unique value in a column of data. In this example, COUNTDISTINCT returns the number of times a specific region was recorded in the data to reflect the times there was a sale in that region.

COUNTDISTINCT(values1)

  • values1: set to the Regions data in column A (A:A).

Using GROUPBY to create a spark line, spark bar, or win/loss chart

GROUPBY can be used to format your data in a comma separated list to use those values in a spark line, spark bar, or win/loss chart. This is useful for aggregating multiple unique elements in your data source and then plotting them using one of the mini chart formats.

GROUPBY( values1, values2,"method" )

  • values1: set to the Regions data in column A (A:A).
  • values2: set to the Units data in column B (B:B).
  • method: set to "join(values)".