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.