Multiplication Between Columns in the same table

[kelly008]

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:

TEST1 = SUM('RelProdOrderComponents'[Expected_Quantity]) * SUM('RelProdOrderComponents'[Unit_Cost])

This provides the correct line data, but the total is wrong.

As you can see the line data is right, but the total is way out. 

 -----------------------------------------------------------------------------------------------------------

The second test was using a custom column with the following formula:

Test2 CustomColumn = [Expected_Quantity]*[Unit_Cost]

This however, gives some of the correct line data and does give a more realistic total at the bottom:

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]

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:

[SpartaBI]

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 haha

[kelly008]

Hi,

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. 

[SpartaBI]

@kelly008 ok, try this:

MEASURE = 
SUMX(
     VALUES('RelProdOrderComponents'[Description]),
     CALCULATE(SUM('RelProdOrderComponents'[Expected_Quantity]) *
     CALCULATE(SUM('RelProdOrderComponents'[Unit_Cost]))
)

Showcase Report – Contoso By SpartaBI

      

[kelly008]

Hi,

So this goes to the other method that I tried in 'Test2 CustomColumn' where it appears to get the total right but not the row data:

As you can see, the line with the red X is not calculating correctly, it should be doing 18 * £334.24 which is £6016.29 (If you look at the column TEST 1, you can see the correct row values) and not £1894.24.  

[kelly008]

Hi, just to add on here, although there is a red tick next to the total, it still isnt right even though the number is more realistic than 600 million. Sorry if that confuses anything.

[SpartaBI]

ok wait 🙂 in your visual, excpected qunatity and unit cost are implicit measures right? You just added the columns there, right? What is the summarization of these columns? SUM / AVERAGE / Something else?

[kelly008]

So.... Both are just columns from the RelProdOrderComponent.

Summarisation wise:

[SpartaBI]

Great! It's just SUM, so I have no clue 🤣, I will PM you

[SpartaBI · Accepted solution]

@kelly008 I think you mean you want to do something like that:

MEASURE = 
SUMX(
     'RelProdOrderComponents',
     'RelProdOrderComponents'[Expected_Quantity] *
     'RelProdOrderComponents'[Unit_Cost])
)
 

Showcase Report – Contoso By SpartaBI

      

[kelly008]

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'.

[SpartaBI]

@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