Power BI Measure multiply with examples

In this Power BI Tutorial, we will learn all about Power BI Measure multiply. Also, we will discuss:

  • Power BI Measure multiply two columns using DAX
  • Power BI Measure multiply by 100
  • Power BI Measure multiply two columns from different tables
  • Power BI Measure multiply column by Measure

Power BI Measure multiply

In Power BI, there is no multiply function in DAX. We use the arithmetic function(Asterisk) ‘ * ‘ for multiplication.

When we passed any argument as a string, it will convert into numbers automatically.

let’s have an example, here we will take two arguments as strings.

Multiplication = "5"*"10"
Power BI Measure multiply
Power BI Measure multiply

Power BI Measure multiply two columns using DAX

In Power BI, a multiplication operator returns the multiply value of two numbers.

The syntax for the multiplication is:

(<column1> * <column2>)

For example, we have created a simple table like the below:

Power BI Measure multiply two columns
Power BI Measure multiply two columns

Now will create a measure to calculate the multiplication of two values:

Multiplication = CALCULATE(SUM('Table'[value-1])) * SUM('Table'[value-2])  
Power BI Measure multiply
Power BI Measure multiply

We can calculate the multiplication not only in positive numbers but also in negative numbers as shown in the table.

This is how to calculate the multiplication by using Power BI Measure.

Read Power bi measure count with filter

Power BI Measure multiply by 100

Now we will see how to create a measure that will return the result as multiply by 100.

For this here we have created a table like below:

Power BI Measure multiply by 100
Power BI Measure multiply by 100

Now we will create a measure, that will calculate if the year is greater than or equal to 2020, then the investment amount will multiply by 100. Otherwise, it will remain the same.

Profit Amount = IF(SELECTEDVALUE('Investment Table'[Year]) >= 2020, CALCULATE (SELECTEDVALUE('Investment Table'[Investment]) * 100, FILTER('Investment Table', 'Investment Table'[Year] >= 2020)), SELECTEDVALUE('Investment Table'[Investment]))  
Power BI Measure multiply by 100
Power BI Measure multiply by 100

Except for those years (which are less than 2020), all amounts got increased i.e. multiplied by 100 from the investment amount.

This is how to calculate Power BI Measure multiply by 100.

Read Countif function in Power BI Measure

Power BI Measure multiply two columns from different tables

Now we will see how to calculate the multiplication in between two columns from different tables. Here are step-by-step guides to implementing this.

Step-1:

For example, we have created two simple tables. One is based on the Product’s ID and quantity. Another is based on the Product’s details such as ID, name, and price.

Table1- Product table

Calculate measure multiply two column
Calculate measure multiply two column

Table2- Product’s details

Calculate measure multiply two column
Calculate measure multiply two column

Step-2:

Now we will create a relationship between two tables to calculate the accurate result and display the correct information.

On Power BI, go to model view > Product table > more option > manage relationship

Measure multiply two columns from different tables in Power BI
Measure multiply two columns from different tables in Power BI

A manage relationship page will open. Click on New. Here we will create a relationship between ProductID(from Product table) and ID(Product details). Then click on OK.

Measure multiply two columns from different tables in Power BI
Measure multiply two columns from different tables in Power BI

Now we can see a relationship is created in between those two tables like this:

Power BI Measure multiply two columns from different tables
Power BI Measure multiply two columns from different tables

Step-3:

Now we will create a measure to calculate the multiply of two columns from different tables.

Measure = SUM('Product table'[Quantity])* SUM('Product details'[Price])
Power BI Measure multiply two columns from different tables
Power BI Measure multiply two columns from different tables

We can see that the measure calculated the total amount by multiplying the price and quantity of the products from two different tables.

This is how to calculate using Power BI Measure to multiply two columns from different tables.

Read Power bi measure switch statement

Power BI Measure multiply column by Measure

Now we will see how to create a measure that returns the multiplies of a column in Power BI. For this, we have created a simple table based on the Product, Quantity Sold, and Unit price.

Microsoft Power BI multiply column with Measure

So here we will create a measure that will calculate Total Cost = [Cost Per Unit] * [Total Quantity Sold]

Step 1- First we will calculate, the Total Quantity sold, for this create a New measure, and then provide the below formula.

Total Quantity sold = Sum('Table'[Quantity sold])
Microsoft Power BI Measure multiply column with Measure

Step 2: Next we will multiply the Total Quantity Sold by the unit price, so it will return the cost price, for each product. As we are using Measure, nor fit for row-level calculation, to get the correct Total cost, follow the below steps

First create a new measure, in Power BI, and then provide the below formula

Result = SUMX('Table', [Total Quantity sold] * Sum('Table'[Unit Price]))
Power BI Measure multiply column with Measure

Now we will calculate the Total cost so for this create another measure, and then provide the below formula:

total cost = Sumx('Table', [Result])
Microsoft Power BI Measure multiply column by Measure

To show the result add a Table visual to the canvas and then add the Product, Unit Price, Quantity sold, and Total cost measure.

Power BI Measure multiply column by Measure

This is how we can multiply column by Measure using Power BI Measure.

You may like the following Power BI tutorials:

Conclusion

From this Power BI Tutorial, we learned below these topics:

  • What is Power BI Measure multiply?
  • How to do multiply between two columns using DAX in Power BI Measure?
  • How to calculate Power BI Measure multiply by 100?
  • How to evalate multiply two columns from different tables in Power BI Measure?
  • How to calculate multiply column by Measure using Power BI Measure?
  • Hi Bhawana, Thank you for the explanation. For the last example, the total price and new measure of the column do not match the above figures. Could you please explain how to get that right?

  • Your multiplication measure does not work over large sets of data. You need to use SUMX. You can test by adding any of the rows to bottom table. Your formula adds new row of same data and multiplies resulting in higher amount.

  • HI, the new measure created with sumx also shows a strange sum at the bottom 83444 if you quickly sum up the values uptop you end up with less then 8k. How to get that measure working w/o creating a calc. column?

  • >