A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
The PRODUCTX function is one of the many DAX functions available in Power BI. This function is used to multiply two or more values together and return the product as a result. In this article, we will explore how to use the PRODUCTX function and provide some practical examples.
Syntax of the PRODUCTX Function
The syntax of the PRODUCTX function is as follows:
PRODUCTX(❰table❱, ❰expression❱)
– `❰table❱`: This parameter specifies the table or tables that contain the values to be multiplied. It can be a table name, a table expression, or a function that returns a table.
– `❰expression❱`: This parameter specifies the expression that calculates the values to be multiplied. It can be a column name, a measure, or any DAX expression that returns a scalar value.
Example 1: Multiplying Two Values
Suppose we have a table named “Sales” that contains two columns: “Quantity” and “Price”. We want to create a new column that calculates the total revenue for each sale. We can use the PRODUCTX function to achieve this as follows:
Total Revenue = PRODUCTX(Sales, Sales[Quantity] * Sales[Price])
In this example, we are multiplying the values in the “Quantity” and “Price” columns for each row in the “Sales” table and returning the product as the total revenue.
Example 2: Multiplying Values from Multiple Tables
Suppose we have two tables named “Sales” and “Discounts”. The “Sales” table contains the columns “Product”, “Quantity”, and “Price”, while the “Discounts” table contains the columns “Product” and “Discount”. We want to create a new column in the “Sales” table that calculates the total revenue after applying the discounts. We can use the PRODUCTX function along with the RELATED function to achieve this as follows:
Total Revenue after Discounts =
PRODUCTX(Sales,
Sales[Quantity] * Sales[Price] *
(1 - RELATED(Discounts[Discount]))
)
In this example, we are multiplying the values in the “Quantity” and “Price” columns for each row in the “Sales” table and multiplying it by the corresponding discount value in the “Discounts” table (using the RELATED function to retrieve the discount value). The result is the total revenue after applying the discounts.
Example 3: Using the FILTER Function
Suppose we have a table named “Orders” that contains the columns “Order Date”, “Product”, “Quantity”, and “Price”. We want to create a measure that calculates the total revenue for a specific product in a specific year. We can use the PRODUCTX function along with the FILTER function to achieve this as follows:
Total Revenue =
PRODUCTX(
FILTER(
Orders,
Orders[Product] = "Product A" &&
YEAR(Orders[Order Date]) = 2021
),
Orders[Quantity] * Orders[Price]
)
In this example, we are filtering the “Orders” table to only include rows where the product is “Product A” and the year is 2021. We are then multiplying the values in the “Quantity” and “Price” columns for each row in the filtered table and returning the product as the total revenue.
The PRODUCTX function is a powerful tool in Power BI that allows users to multiply values from one or more tables and return the product as a result. By using this function along with other DAX functions such as FILTER and RELATED, users can create custom calculations and aggregations based on their data.