Forum Discussion
Multiplication Between Columns in the same table
Hello,
I am currently trying to multiply two columns in the same table, but when I do I get the wrong total's at the bottom or the wrong line data depending on the formula's I've tried to use.
From this table, all I want to do is multiply the 'Expected_Quantity' Column by the 'Unit_Cost' column.
I have tried two different tests to get this to work, the first being to create a measure with the following formula:
As you can see the line data is right, but the total is way out.
But, as you can see not all of thes row are correct.
Can I please have some help with how I get the table to have the correct line and total data.
Thanks!
kelly008 I think you mean you want to do something like that:
MEASURE = SUMX( 'RelProdOrderComponents', 'RelProdOrderComponents'[Expected_Quantity] * 'RelProdOrderComponents'[Unit_Cost]) )
12 Replies
kelly008 I think you mean you want to do something like that:
MEASURE = SUMX( 'RelProdOrderComponents', 'RelProdOrderComponents'[Expected_Quantity] * 'RelProdOrderComponents'[Unit_Cost]) )Hi,
Thanks for replying, when I use the code you have provided I get this:
Oddly, If I take the last bracket out I get this:
Which basically just adds in another column that has the same value as column 'Test2 CustomColumn'.
kelly008 you had a bracket down down below 🙂
Anyway, so I didn't understand what you want to achieve.
Can you share the visual you have without testing and just the column of the desired result by drawing the logic and the expected number.
Also, Expected_Quantity] and [Unit_Cost] are they measures or columns? If measures what are they?
Is [description] a key in your table or a value that represnet multile rows?
Can you share also a screenshot of the data model table
Hi, Yes i did haha thanks for noticing 🙂
So, essentially this table is a summary of materials used against a construction project. The goal is to show exactly how much was spent on materials per material type in rows (Description column) as well as on a whole via the total of unit cost * expected quantity columns.
'Expected quantity' & 'Unit cost' are columns within the 'RelProdOrderComponents' Table.
The data table is this:
by your latest picture you want the total to be equal to:
10270 * 5713009 = 586672602430 ?
Cause that what you get in the original post and said it's not what you want.
You will get this with indeed what you wrote in the beginning:
TEST1 = SUM('RelProdOrderComponents'[Expected_Quantity]) * SUM('RelProdOrderComponents'[Unit_Cost])
What am I missing here? I guess I still just don't understand what you want to get hahaHi,
Sorry for the confusion, I was simply drawing out how the formula is going as per your request.
The total itself however is very very wrong.
What I want is for the Test 1 Formula to actually provide the correct total, the rows are correct just not the total at the bottom. If you do the calculation outside of PowerBI it should roughly be 6 million not 586 million.
