Multiplication between Tables

[kelly008]

Hello,

I currently have 2 values from 2 seperate tables, I am trying to multiply them together but when I do, the total for the column is way out:

For some reason it is 44,964.38 when it should actually just be a sum off the values in the Row which is roughly 500?

This is the current measure I am using: 

Quantity_per_Unit x Quantity =

SUM('SYNBOMDetails'[Quantity_per_Unit]) * SUM('SYNProdOrderLine'[Quantity])

Any help would be much appreciate. Thanks!

[Anonymous · Accepted solution]

Hi @kelly008 ，

Is there any relationship created between the table SYNProdOrderLine and SYNBOMDetails? If so, which fields are they based on? Could you please provide me with the Fields pane settings for your table visual?

Relationship info

Fields pane setting

You can create a new measure based on the measure [Quantity_per_Unit x Quantity] as shown in the figure below, please find the details in the attachment.

Measure = SUMX ( VALUES ( Field with Text type on the table visual except for the field [Quantity_per_Unit],[Quantity] and the measure [Quantity_per_Unit x Quantity]  ), [Quantity_per_Unit x Quantity] )

Best Regards

[amitchandak]

@kelly008 , if they are related like master details

then you can have

Quantity_per_Unit x Quantity =
 SUMX('SYNProdOrderLine, 'SYNProdOrderLine'[Quantity] * related('SYNBOMDetails'[Quantity_per_Unit] ) )

if not then have some common dimension to multiply

Assume order as common dimension

sumx(value(Order[Order_id]), calculate( SUM('SYNBOMDetails'[Quantity_per_Unit]) * SUM('SYNProdOrderLine'[Quantity]) ) )