ADDCOLUMNS
Returns the table you give, with extra computed columns added to each row.
ADDCOLUMNS(<table>, <name>, <expression> [, <name>, <expression>] ...)Returns: A table.
What it does
ADDCOLUMNS takes a table and adds one or more computed columns to every row. Each expression is evaluated for each row, so a measure gives the total for that row's context. Unlike SUMMARIZE, it does not group. It keeps every row of the table you pass in. Paired with VALUES, it keeps every member even when a total is zero.
Syntax and arguments
- table: the table to extend, such as
VALUES(Product[ProductCategory]). - name: the new column name, in quotes.
- expression: the value for each row, often a measure.
- Repeat name and expression pairs to add more columns.
Example
A category scorecard with sales and margin next to each category name, written as a DAX query. Run it with EVALUATE so the measures respond to the report's filters.
EVALUATE
ADDCOLUMNS (
VALUES ( Product[ProductCategory] ),
"Sales", [Total Sales],
"Gross Margin %", [Gross Margin %]
)
Each row is a category, and the two new columns hold its sales and margin percentage. A category with no sales in the current filter still appears, with a blank or zero figure.
Example: a second use
Each country's sales and its share of company sales, in one query.
EVALUATE
ADDCOLUMNS (
VALUES ( Stores[Country] ),
"Sales", [Total Sales],
"Share of Company", DIVIDE (
[Total Sales],
CALCULATE ( [Total Sales], REMOVEFILTERS ( Stores ) )
)
)
The denominator removes the store filter, so every row divides by the company total for the other filters on the page.
Things to watch
- ADDCOLUMNS keeps rows where the measures are zero or blank.
SUMMARIZECOLUMNSdrops them. - Inside a calculated table, measures are computed once at refresh, with no slicers. Use calculated tables for fixed lists, and use
EVALUATEfor live numbers. - Column names must be unique in the result.
- When the output needs to be used in other measures, move the logic into a measure. A query result cannot be sliced in a report.
In business terms
Scorecards that list every entity or account with its totals, exception lists with a computed flag, and any table where the members matter as much as the numbers. A common check: list every store and show its sales, including the stores with none.