Power Pivot Principles: Introducing the ADDCOLUMNS Function

Welcome back to the Power Pivot Principles blog.  This week, we are going to look at the ADDCOLUMNS function.

We introduced the SUMMARIZE function in a previous blog.  This function returns a summary table for the requested totals over a set of groups.  The SUMMARIZE function can add columns with calculated values.  This week, we are going to create extend this idea by using the functions ADDCOLUMNS and SUMMARIZE.

The ADDCOLUMNS function adds calculated columns to the given table or table expression.  It uses the following syntax to operate:

• The <table> is any DAX expression that returns a table of data
• The <name> is a name given to the column, enclosed in double quotes
• The <expression> is any DAX expression that returns a scalar expression, evaluated for each row of the table.

Consider the following data table (not displayed in full):

This data table contains the sales amount for a specific date. We also create another calendar table in PowerPivot by using the method introduced here:

The two tables may be linked:

We create the measure using SUMMARIZE function with the “extended” column, Monthly Sales.

The formula SUMMARIZE above, summarize the table SalesData at the Year and Month level.  The value aggregated is the sum of SalesAmount and the column name is defined as Monthly Sales.

We will evaluate the expression in table attribute Edit DAX as introduced here.   The result would be (not displayed in full):

We can achieve a similar result by using the ADDCOLUMNS function. The syntax operates as:

ADDCOLUMNS works like SUMMARIZE. In the case above, it took each combination of Year and Month as a coordinated filter context and used the table returned from SUMMARIZE as the base for Monthly Sales calculation. The result would be the same:

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.