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 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:
Now will create a measure to calculate the multiplication of two values:
Multiplication = CALCULATE(SUM('Table'[value-1])) * SUM('Table'[value-2])
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:
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]))
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
Table2- Product’s details
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
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.
Now we can see a relationship is created in between those two tables like this:
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])
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.
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])
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]))
Now we will calculate the Total cost so for this create another measure, and then provide the below formula:
total cost = Sumx('Table', [Result])
To show the result add a Table visual to the canvas and then add the Product, Unit Price, Quantity sold, and Total cost measure.
This is how we can multiply column by Measure using Power BI Measure.
You may like the following Power BI tutorials:
- Power bi measure divide
- Power BI Measure Date
- Power BI Measure SUM
- Power bi measure by category
- Power BI Measure IF with Examples
- Power bi measure subtract
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?
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com
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?