Create measure with many to many relationship

[Anonymous]

Hi, 

I'm a neewbie here on PBI, could somone please help me ? 

I've created a many to many relationship with two tables :

I'm trying to get the total quantity each month by the production status so I've created a measure to display on a stacked bar graph : 

Mesure = SUM(Material[Quantity])*SUM('Product'[Quantity])

When filtering on the product and the material, quantities add up but not in global :

e.g. : januray 

- 12 table legs when table filter is on (ok)

- 6 chair legs when chair filter in on (ok)

- 32 legs when no filters are used (not ok)

Could someone please give me a hand on this ?

https://drive.google.com/file/d/1kgcNz_bSX0IOpmuW7TSB-DP0w0zWH0HH/view?usp=sharing

Thanks, 

Charlotte

[amitchandak · Accepted solution]

@Anonymous , Create a common product and date Table,

Then you can try like

Mesure = SUMX(Values(Product[Product]) , calculate(Sum(Material[Quantity])*SUM('Product'[Quantity])))

or with the current one

Mesure = SUMX(Values(Material[Product]) , calculate(Sum(Material[Quantity])*SUM('Product'[Quantity])))