How to use ADDCOLUMNS and SUMX in a Measure

DAX can be difficult to learn because some of the more advanced functions have limited use cases. However, I recently stumbled across a great use-case for using both ADDCOLUMNS and SUMX in the same measure. As a bonus, the performance gained essentially doubles the speed of the "normal" version of the code! In this article you'll learn how to use ADDCOLUMNS and SUMX in a measure.

Use Case

This technique can be used anytime you are calculating a "percentage of total".

Original DAX Code

New Products % = 
VAR _BlueProductsAmount = 
	CALCULATE (
		[Product A] + [Product B] + [Product C],
		Products[Is New Product] = "Y"
	)

VAR _AllProductsAmount = 
	[Product A] + [Product B] + [Product C]

VAR _Result = 
	DIVIDE ( _BlueProductsAmount, _AllProductsAmount, 0 )

RETURN
	_Result

Improved DAX Code

New Products % (Improved) =

VAR _ProductTypes = 
	VALUES ( Products[Is New Product] ) // You May need ALL() instead of VALUES() depending on what your filter context is.

VAR _AddAmounts = 
	ADDCOLUMNS ( 
		_ProductTypes,
		"@product_a, [Product A],
		"@product_b, [Product B],
		"@product_c, [Product C]
	)

VAR _NewProducts = 
	SUMX ( 
		FILTER ( _AddAmounts, [Is New Product] = "Y" ), 
		[@product_a] + [@product_b] + [@product_c]
	)

VAR _AllProducts = 
	SUMX ( 
		_AddAmounts,
		[@product_a] + [@product_b] + [@product_c]
	)

VAR _Result = 
	DIVIDE ( _NewProducts, AllProducts, 0 )

RETURN
	_Result

Why is the new code better?

In the original code, we are computing the same three measures twice. Yes, technically they are different due to the filter on the the new products, but the DAX engine needs to access the same source all the same. By using ADDCOLUMNS, we can pre-compute and store all the values in a virtual table. Then, SUMX allows us to aggregate (sum) the values from the pre-computed virtual table, saving the engine from having to access the source again.

This code is particularly great for when you are combining multiple measures, like in the example above.

Should I always use this pattern?

This technique is obviously more complex and verbose. Always go for simpler, more readable DAX code. As such, I would discourage the use of this technique unless you need to improve the speed of your measure. Furthermore, this pattern may not increase performance of your measure, and may even make it worse. This is because using ADDCOLUMNS and SUMX utilizes a different part of the DAX engine (the formula engine). If you're running this measure over a column with many unique values (over 1000), this technique will most likely run slower for you.