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:

1. Blanks:
   If there are blank rows, they are ignored: Order ID Sales 1 100 2 BLANK 3 50 Result: ( 100 \times 50 = 5,000 ).
2. 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:

1. 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 )

2. 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:

1. Product of Sales:

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

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

2. 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.