multiply two columns from two tables having many-to-many cardinality

[monojchakrab]

I have two tables (screengrab from relationships attached) but connected with a many-to-many cardinality. 

I need to multiple the [ShippedQty] from the #Table[Amazon YTD] with the [MPU] column from #Table[configuration summary]

But neither RELATED not RELATEDTABLE is working, most certainly because of the M2M cardinality.

relationship summary

Is there any workaround for this?

[monojchakrab]

Thanks @tamerj1 .

I am actually trying to write a measure and use that in a card visual. If I can multiple these two columns it will give me the total qty sold, which is a very important measure for me for this report.

I have tried to write the same code as a measure - it does not work .

But thanks anyways for the quick response

[tamerj1 · Accepted solution]

@monojchakrab 
IN this case you can use

=
SUMX (
    Table1,
    SUMX ( RELATEDTABLE ( Table2 ), Table1[ColumnName] * Table2[ColumnName] )
)

[monojchakrab]

That worked @tamerj1 ...

But I think its somehow giving the wrong result but there is no error with the formula. Thanks.

I think the 2nd nested iterator is playing wonky : If I do a sumproduct in excel, I am getting 4383 units (which is correct), whereas thru' this formula, I am getting a figure of 284.1 K, which is incorrect.

Any idea why this could be happening?

Also, If I try using LOOKUPVALUE, to pull in Table1[required column] into Table2, it is giving an error

[tamerj1]

@monojchakrab 
Is it possible to provide sample data and the expected result based on it?

[monojchakrab]

Title	MPU	Active	Form	Size
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1	Stevia	Sachets	100 count

Title	Shipped Quantity
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 50 Sachets, Pack of 5	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachets - Pack of 1	1
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 50 Sachets, Pack of 3	1
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 4	1
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 2	1
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 50 Sachets, Pack of 3	1
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 50 Sachets, Pack of 3	1
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 50 Sachets, Pack of 5	1
Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Sachets, Pack of 6	1
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 6	1
Equal Stevia Natural Sweetener, Sugar Free, 50 Sachets - Pack of 2	1
Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 300 Tablets, Pack of 2	1
Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Sachets, Pack of 6	1
Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Sachets, Pack of 2	1
Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 3	1
Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Sachets, Pack of 6	1

What I want is as follows :

Multiply the MPU from from Table 1 for every title matching in 2nd table with the shipped qty from table 2 and return the total sum of this multiplication.

Does that work for you?

[tamerj1]

Hi @monojchakrab 
Sorry for thr late reply 
The tilte in table1 has no title match from table2. Please provide relevant sample of data. Thank you.

[monojchakrab]

In fact the Title column in Table1 references the same column in Table 2 - so they are identical actually.

However, I am copying the table again ;

Title	MPU
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1
Equal Stevia Natural Sweetener, Sugar Free, 100 Sachet, Pack of 1	1

Not sure if this works for you...Is there a way I can send over the PBIX file itself and you can check out the model?

[tamerj1]

Hi @monojchakrab 

Assuming that you are creating a calculated column in Table1 then

SUMX (

RELATEDTABLE ( Table2 ),

Table1[ColumnName] * Table2[ColumnName]

)