PRODUCTX DAX Function (Aggregation)
Returns the product of an expression values in a table.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
|
Table Iterator |
Table over which the Expression will be evaluated. |
|
|
Expression Row Context |
Expression to evaluate for each row of the table. |
Return values
The product of the Expression evaluated for each row in the Table.
Remarks
PRODUCTX ignores blank expressions, the multiplication operator does not.
By using
-- Apply PRODUCTX to Table[Column]
PRODUCTX ( Table, Table[Column] )
-- Apply an hypothetical DIVIDEX function to Table[Column] by using PRODUCTX
PRODUCTX (
Table,
-- Avoid the IF in the iterator for performance reasons
Table[Column] ^ (1 - 2 * ( Table[RowNumber] > 1 ) )
)
» 1 related function
Examples
-- PRODUCT is the short version of PRODUCTX
-- PRODUCTX multiplies values in the rows it scans
--
-- The report shows the actualized sales in 2009 by applying
-- the inflation rate in sales made in previous years
DEFINE
MEASURE Rates[Compound Inflation Rate] =
PRODUCTX ( Rates, 1 + Rates[InflationRate] )
MEASURE Sales[Sales Amount] =
SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
MEASURE Sales[Actualized Sales 2009] =
SUMX (
VALUES ( 'Date'[Calendar Year Number] ),
VAR ReferenceYear = 2009
VAR BaseYear = 'Date'[Calendar Year Number]
VAR CompoundRate =
CALCULATE (
COALESCE ( [Compound Inflation Rate], 1 ),
Rates[Year] >= BaseYear && Rates[Year] < ReferenceYear
)
RETURN [Sales Amount] * CompoundRate
)
MEASURE Sales[Adjustment %] =
VAR ReferenceYear = 2009
VAR BaseYear = SELECTEDVALUE ( 'Date'[Calendar Year Number] )
VAR CompoundRate =
CALCULATE (
COALESCE ( [Compound Inflation Rate], 1 ),
Rates[Year] >= BaseYear && Rates[Year] < ReferenceYear
)
RETURN IF (
NOT ISBLANK ( BaseYear ) && NOT ISBLANK ( [Sales Amount] ),
CompoundRate
)
EVALUATE Rates
EVALUATE
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL ( 'Date'[Calendar Year], "IsTotal" ),
"Sales Amount", [Sales Amount],
"Adjustment %", [Adjustment %],
"Actualized Sales 2009", [Actualized Sales 2009]
)
ORDER BY [IsTotal], [Calendar Year]
| Year | InflationRate |
|---|---|
| 2,005 | 0.03 |
| 2,006 | 0.03 |
| 2,007 | 0.03 |
| 2,008 | 0.04 |
| 2,009 | 0.00 |
| 2,010 | 0.02 |
| Calendar Year | IsTotal | Sales Amount | Adjustment % | Actualized Sales 2009 |
|---|---|---|---|---|
| 2007-01-01 | false | 11,309,946.12 | 106.80% | 12,078,959.12 |
| 2008-01-01 | false | 9,927,582.99 | 103.84% | 10,308,802.18 |
| 2009-01-01 | false | 9,353,814.87 | 100.00% | 9,353,814.87 |
| (Blank) | true | 30,591,343.98 | (Blank) | 31,741,576.16 |
Related articles
Learn more about PRODUCTX in the following articles:
-
Computing the future value of an investment based on compound growth in DAX
This article describes how to write efficient DAX expressions that compute the compound interest of incremental investments made throughout the holding period. » Read more
-
Using EXPAND and COLLAPSE in visual calculations
This article provides examples of visual calculations where the use of EXPAND and COLLAPSE is required to obtain the correct result. » Read more
Related functions
Other related functions are:
Last update: Nov 19, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Ashley Garoutte, Steve Yamashiro, Kenneth Barber
Microsoft documentation: https://docs.microsoft.com/en-us/dax/productx-function-dax