How to multiply a column by a constant or variable number in Power BI?

Step 1: Identify the column to multiply

In this example, we would like to show how we multiply two columns from different tables in our model. Our goal is to create a column showing the Net Sales Amount for specific Orders.

Here’s a preview of our model:

To make the required calculation, we will multiply two columns:

  • Sales Amount – stored in the Orders table in our model; represents the Order Amount.
  • Commission Percentage – stored in the SalesReps table – represents the commission of the specific sales rep who sold the course.

Note: you can use the same syntax to multiply a numeric column by one or multiple numeric constant / scalar values.

Step 2: Create a new calculated column

  • In the Report View, click the Orders column.
  • From the Ribbon, hit the New Column button.
  • Then type the following DAX code:
Net Sales Amount = Orders[Sales Amount]*(1- SalesReps[Commission Percentage])
  • This will fail with the following message:
A single value for column 'Commission Percentage' in table 'SalesReps' cannot be determined. This can happen when a measure formula refers to a column containing many values....
  • The reason, is that the Commission Percentage is stored in a different table. If we want to calculate the discount for every order we should use the RELATED function to connect between each order and its appropriate discount. Use this instead:
Net Sales Amount = Orders[Sales Amount]*(1-RELATED(SalesReps[Commission Percentage]))

Step 3: Display the column in the report

  • Still in the Report Tab, insert a table visual into your report.
  • The add the following fields: Sales Amount, Commission Percentage and the newly created Net Sales Amount column.

Here we go:

Follow up learning

Calculate number of days and hours between two dates in Power Bi reports.