Understanding PRODUCT and PRODUCTX in DAX

Understanding `PRODUCT` and `PRODUCTX` in DAX

1. `PRODUCT` Function

Returns the product of all numeric values in a column.
Ignores blanks and non-numeric values.

Example: Total Sales Multiplication

We want to calculate the product of all values in the Sales column.

Dataset:

Order ID	Sales
1	100
2	200
3	50

DAX Formula:

Total Sales Product = PRODUCT('Orders'[Sales])

Calculation:

( 100 \times 200 \times 50 = 1,000,000 )

Result:

1,000,000

Edge Cases:

Blanks:
If there are blank rows, they are ignored: Order ID Sales 1 100 2 BLANK 3 50 Result: ( 100 \times 50 = 5,000 ).
Zero:
If any row contains 0, the result will be 0: Order ID Sales 1 100 2 0 3 50 Result: ( 100 \times 0 \times 50 = 0 ).

2. `PRODUCTX` Function

Returns the product of an expression evaluated for each row in a table.
Can handle complex calculations for each row.

Example: Product of Profit Values

We want to calculate the product of profit for each order, where profit is calculated as Sales - Discount.

Dataset:

Order ID	Sales	Discount
1	100	10
2	200	20
3	50	5

DAX Formula:

Total Profit Product = 
PRODUCTX(
    'Orders',
    'Orders'[Sales] - 'Orders'[Discount]
)

Row-Wise Calculation:

Order ID	Profit (Sales – Discount)
1	100 – 10 = 90
2	200 – 20 = 180
3	50 – 5 = 45

Calculation:

( 90 \times 180 \times 45 = 729,000 )

Result:

729,000

Edge Cases:

Blanks or Missing Values:
If any row has blank values in the calculation: Order ID Sales Discount 1 100 10 2 BLANK 20 3 50 5 Result: Only rows with valid numeric results are included. In this case:

( 90 \times 45 = 4,050 )

Zero Values:
If any row evaluates to 0: Order ID Sales Discount 1 100 100 2 200 20 3 50 5 Result: ( 0 \times 180 \times 45 = 0 ).

Comparison of Functions

Function	Purpose	Handles	Example Use Case
`PRODUCT`	Multiplies all values in a column.	Ignores blanks, includes zeros.	Calculate the total sales product.
`PRODUCTX`	Multiplies the result of a row-wise calculation.	Evaluates a complex expression.	Calculate the total profit product.

Combined Example (Superstore Dataset)

Dataset:

Order ID	Sales	Discount	Cost
1	100	10	80
2	200	20	150
3	50	5	40

DAX Measures:

Product of Sales:

   Product of Sales = PRODUCT('Orders'[Sales])

Result: ( 100 \times 200 \times 50 = 1,000,000 ).

Product of Profit:

   Product of Profit = 
   PRODUCTX(
       'Orders',
       'Orders'[Sales] - 'Orders'[Discount] - 'Orders'[Cost]
   )

Profit Calculation:

( (100 – 10 – 80) = 10 )
( (200 – 20 – 150) = 30 )
( (50 – 5 – 40) = 5 ) Result:
( 10 \times 30 \times 5 = 1,500 ).

These examples illustrate how PRODUCT and PRODUCTX can be used to perform both simple and complex multiplicative calculations on your data in Power BI.

Understanding PRODUCT and PRODUCTX in DAX