SUMMARIZECOLUMNS, SUMMARIZE, GROUPBY

03 GROUPBY

02 SUMMARIZE

01 SUMMARIZECOLUMNS

(Table manipulation functions )

SUMMARIZECOLUMNS

Returns a summary table.

Use it, if you want to add new table.

Remarks

• This function does not guarantee any sort order for the results.

• A column cannot be specified more than once in the groupBy_columnName parameter.

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Example 1

returns a distinct values from a column.

Example 2

returns a filtered table.

Example 3

returns a filtered table with a subtotal for gender column where country is UK

Example 4

using multiple expressions with filtered table.

Example 5

With IGNORE

Table is grouped by CustomerID column. If you use IGNORE, a categories with blank values will be return.

Example 6

With ROLLUPADDISSUBTOTAL

ROLLUPADDISSUBTOTAL returns a grand total per CustomerID. CustomerID can be displayed just ones in the formula, otherwise we got error. Sum function returns a subtotal, together with IGNORE returning the blank values.

SUMMARIZE

Returns a summary table.

Syntax:

SUMMARIZE (<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)

Parameters

table Any DAX expression that returns a table of data.

groupBy_ColumnName(Optional) The qualified name of an existing column used to create summary groups based on the values found in it. This parameter cannot be an expression.

name The name given to a total or summarize column, enclosed in double quotes.

expression Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).

Example 1

returns unique values.

Example 2

Emp : table name

Emp[Country]: column name I want to use to summarize (group) the data.

"Salary by Country" : New added column

SUM(Emp[Salary]): type of aggregation for the grouped data.

Example 3

with FILTER

Example 4

with FILTER

Filters: Continent is Europe, Gender is Female

Summarizing by Country column

Example 5

with ROLLUP

Example 6

with ISSUBTOTAL

"Total by Gender" : new column name

ISSUBTOTAL(Emp[Gender]) : function and column name for which I want to return true or false

GROUPBY

The GROUPBY function is similar to the SUMMARIZE function. However, GROUPBY does not do an implicit CALCULATE for any extension columns that it adds.

Example 1

returns a single column, list of the unique values.

Example 2

With CURRENTGROUP

The CURRENTGROUP function takes no arguments and is only supported as the first argument to one of the following aggregation functions: AVERAGEX, COUNTAX, COUNTX, GEOMEANX, MAXX, MINX, PRODUCTX, STDEVX.S, STDEVX.P, SUMX, VARX.S, VARX.P.

As you can see from this picture below, GROUPBY is not collaborating with all the aggregate functions. It supports only “X” aggregations function with CURRENTGROUP function.

working...

Example 3

Nested GROUPBY

I want to return these max values by region

3.1

3.2 Nested GROUPBY

GroupBy NESTED =

GROUPBY (

GROUPBY (

salesCat,

salesCat[Region],

salesCat[Category],

salesCat[Item],

"Units", SUMX ( CURRENTGROUP (), salesCat[Units] )

),

salesCat[Region],

"MAX UNITS SOLD", MAXX ( CURRENTGROUP (), [Units] )

)

Example 4

GROUPBY with Filter

GROUPBY with Filter =

FILTER (

GROUPBY (

salesCat,

salesCat[Region],

salesCat[Category],

"Total sales", SUMX ( CURRENTGROUP (), salesCat[Units] * salesCat[UnitCost] )

),

salesCat[Region] = "West"

)

Example 5

GROUPBY with ADDCOLUMNS

this combination of code supports CALCULATE function and there's no need to use CURRENTGROUP function.

SYNTAX

ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)

PARAMETERS

Term Definition

table Any DAX expression that returns a table of data.

name The name given to the column, enclosed in double quotes.

expression Any DAX expression that returns a scalar expression.

GROUPBY with ADDCOLUMNS =

ADDCOLUMNS (

GROUPBY ( Emp, Emp[Country], Emp[Gender] ),

"HEADCOUNT", CALCULATE ( COUNT ( Emp[Name] ) ),

"TOTAL SALARY", CALCULATE ( SUM ( Emp[Salary] ) )

)

Note: must use CALCULATE, otherwise SUM function is not working correctly.

Example 6

GROUPBY with ADDCOLUMNS and FILTER

GROUPBY wth ADDCOLUMNS FILTER = FILTER( ADDCOLUMNS( GROUPBY(salesCat,salesCat[Region],salesCat[Category]), "COUNT UNITS SOLD",CALCULATE(SUM(salesCat[Units]))), salesCat[Region]="West")