Yes. In Power BI, use an iterator such as SUMX to multiply two columns row by row and add the results: Total Amount = SUMX(Sales, Sales[Quantity] * Sales[Unit Price]). Replace the table and column names with yours; the measure will then respond to report filters and slicers.
The basic DAX measure
This pattern is useful when each row represents an item, transaction, or other record with two numeric values that should be multiplied together. For example, if a Sales table contains Quantity and Unit Price, the expression multiplies those values for each Sales row and totals the products. In Power BI Desktop, create a measure by selecting the table in the Data pane and choosing New measure, or use the New measure command on the ribbon.
With a table named Sales and columns named Quantity and Unit Price, enter:
Total Amount = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])
Suppose the table has three rows: 2 units at 10, 3 at 4, and 1 at 25. The measure returns 57 because it calculates 2 × 10 + 3 × 4 + 1 × 25. Put the measure in a card or a table visual to see its result. If you add a product, date, or region field to the visual, the result is calculated for the records in that filter context.
Why SUMX is used
SUMX is an iterator: it evaluates an expression for each row in a table, then sums those evaluations. Its first argument is the table to iterate, and its second argument is the expression to evaluate for each row. In this example, the expression is the product of the row’s Quantity and Unit Price.
A measure does not automatically have a current row in the way a calculated column does. A visual might filter Sales to a month or a category, but that filter context can contain many rows. SUMX creates row context while it visits those rows, so DAX can pair the quantity and price from the same record. The result is then aggregated for the current visual cell.
This is why a measure generally should not be written as Sales[Quantity] * Sales[Unit Price] on its own. If several rows are in context, DAX cannot choose a single quantity and a single unit price without an aggregation or an iterator, and it may return an error about a single value not being determined.
Why multiplying column totals is different
A common mistake is to multiply the sum of one column by the sum of the other. With Quantity in Sales[Quantity] and Unit Price in Sales[Unit Price], that expression is:
Total Amount (Incorrect) = SUM(Sales[Quantity]) * SUM(Sales[Unit Price])
For the three example rows, this gives (2 + 3 + 1) × (10 + 4 + 25), or 234—not 57. It combines the total quantity with the total of all prices, rather than multiplying values paired on each row. Multiplying aggregated columns can be correct when that is genuinely the business calculation—for example, a total count multiplied by one shared rate—but it is not a substitute for summing row-level products.
Use SUMX when the intended calculation is “multiply the two values on each record, then total those results.” Think about the business meaning and the level at which the values belong before choosing an expression. A formula can be syntactically valid yet answer a different question than the report requires.
How filters and totals affect the result
Measures are evaluated in filter context. If a chart shows one bar per product, the measure is evaluated separately for each product using the rows that remain visible for that bar. A date slicer, page filter, or report filter can further narrow those rows. The formula itself does not need to name every slicer; the model and visual provide the relevant context.
Grand totals are also evaluated as measures, not necessarily calculated by adding the displayed row values. For the SUMX expression above, the grand total iterates the rows in the total’s filter context and sums their products. This generally matches the sum of the product-level results, provided the visual groups the same underlying records and the measure has no additional context-changing logic.
If a result changes unexpectedly, check which filters apply and whether the visual groups data at the level you expect. A measure that multiplies price by quantity should usually be based on the transaction or line-item table. If quantity and price are stored at different grains, such as daily totals in one table and individual prices in another, simply iterating one table may not represent the intended pairing.
When the columns are in different tables
The simplest multiplication uses columns from the same fact table, where each row already contains the values that belong together. If one value is stored in a related lookup table—for example, Sales has Quantity and Product has Unit Price—the right expression depends on the relationship and the model’s grain.
If each Sales row points to one Product row through a many-to-one relationship, a row iterator over Sales can often retrieve the related price with RELATED. For a Sales table with Quantity and a related Product table with Unit Price, one possible measure is:
Total Amount = SUMX(Sales, Sales[Quantity] * RELATED(Product[Unit Price]))
This assumes an appropriate active relationship from Sales to Product and one matching product row for each sales row. If the relationship is missing, inactive, ambiguous, or points to multiple possible prices, the expression may fail or need a different design. Prices that change over time also require a model that identifies which price applies to each transaction; a simple product lookup may not be sufficient.
Measure or calculated column?
A calculated column can multiply two values on each row and store the result in the model. For the same-table example, the calculated-column expression is Line Amount = Sales[Quantity] * Sales[Unit Price]. You can then create a measure that sums the stored results: Total Amount = SUM(Sales[Line Amount]).
Choose a measure when you need an aggregate that responds to report context and do not need to store each row’s product as a separate model column. Measures are calculated as needed for a visual. Choose a calculated column when the row-level result must be available as a field—for example, to use it in a slicer, grouping, or another row-level calculation. A calculated column uses model storage, and its values are computed during data refresh rather than changing with a report slicer.
- Use
SUMXfor a dynamic total of row-by-row products. - Use a calculated column when the individual row result is itself needed in the model.
- Use a simple aggregation formula only when it matches the actual calculation, not just because it is shorter.
Common problems and checks
If DAX reports that it cannot determine a single value for a column, the expression probably refers to a column without specifying how to aggregate it or without creating row context. For a total of row-level products, use SUMX over the table that contains the paired values. Also check that the column references include the correct table names and that the columns have numeric data types.
Blank or unexpected results can come from blank values, filters that remove all relevant rows, or relationships that do not propagate filters as expected. Decide what a blank should mean in the business calculation. In some data models, a blank quantity or price should leave the row out; in others, it signals incomplete data that should be corrected upstream. Do not automatically replace blanks with zero unless zero is the intended meaning.
Check units and data quality as well. Multiplying a quantity by a unit price produces an amount only if the price is expressed per matching unit and both values use compatible units and currencies. If the source contains text such as currency symbols embedded in the values, clean or convert those fields in the data preparation process rather than relying on a visual format to make text behave like a number.
For a long-running measure, inspect whether the iterator is working over an unnecessarily large table and whether the model has a suitable fact-table structure. SUMX is a standard pattern for row-level calculations, but the table argument should represent the rows that need to be evaluated. Avoid adding a stored column solely to imitate a measure unless you need that column’s row-level behavior.
Formatting and naming the measure
Give the measure a name that describes the result, such as Total Sales Amount or Extended Revenue, rather than a generic name like Result. The name appears in the Fields or Data pane and in visuals, so a clear label helps report users understand what is being shown. If the product of quantity and unit price is revenue before discounts or tax, make that scope clear in the name or the report documentation.
Set the measure’s format in Power BI Desktop so the result is displayed appropriately—as a whole number, decimal, currency, or percentage, depending on what it represents. Formatting changes how the value is shown; it does not change the underlying calculation. For example, displaying a value with two decimal places does not itself round the stored calculation to two decimal places.
Frequently Asked Questions
Can I multiply two columns directly in a DAX measure?
Yes, inside a row context such as the expression evaluated by SUMX. For a total of products across rows, use SUMX(TableName, TableName[Column1] * TableName[Column2]), substituting your actual table and column names.
Why does my measure say a single value cannot be determined?
The measure is likely using a column that has multiple values in the current filter context without an aggregation or row iterator. Use an appropriate aggregation, or iterate the relevant table with SUMX when the calculation needs matching values from each row.
Should I use SUMX or multiply two SUM functions?
Use SUMX when you want to multiply the two values on each row and add those products. Multiplying two separate sums produces a different result in most row-level quantity-and-price calculations.
Can a measure multiply columns from different tables?
It can when the model provides a valid way to match the values, such as a suitable relationship and RELATED inside a row iterator. Confirm that each iterated row maps to the intended value and that the relationship and data grain support the calculation.
Will the measure update when I use a slicer?
Normally, yes. Measures are evaluated in the filter context created by slicers, visual fields, and report filters, assuming those filters reach the table being iterated through the model’s relationships.
Do I need a calculated column instead?
Not for a simple total of row-by-row products. A measure is usually suitable for that aggregate. Create a calculated column if users or other calculations need the individual product stored and available for each row.
Related
- Excel Shortcut to Make All Cells Fit Text Automatically
- Excel Shortcut to Repeat the Last Command or Action
- Excel Shortcut to Select a Column on Windows and Mac
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Archive All Emails in Outlook: Bulk Methods and LimitsTo archive a large number of Outlook emails, select messages in a folder or search results and choose…
- Remove Background Formatting in Word 2007: What to DoIn Word 2007, remove a page background with Page Layout > Page Color > No Color, or remove…
- Excel Split Cells: Did Microsoft Remove the Feature?No—Excel did not get rid of a command that splits one cell into smaller cells; Excel has never…
- How to Calculate CAGR in Excel for 3 YearsTo calculate a three-year compound annual growth rate (CAGR) in Excel, divide the ending value by the beginning…
- How to Double Space in Word for Text or Full DocumentTo double-space text in Microsoft Word, select the text, then choose Home > Line and Paragraph Spacing >…
- How to Hide the Rest of the Columns in ExcelTo hide every column to the right of your data in Excel for Microsoft 365 on Windows, select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.