Many-to-Many Relationship or Bridge Table - Any Measure Does Not Sum Properly

[JaclynPugh2022]

Hi,

I have tried different modeling techniques, first I tried a bridge table between historical orders (received pivot) and future orders, and I used bi-directional filters, which resulted in incorrect quantities exploded in the visual.  Next, I tried a many-to-many relationship between the historical orders and future orders table, and still the measures I'm trying to calculate off the future orders table do not calculate properly. For example, my future orders table shows 830,000 items on future orders Oct-Dec, but if I sum the future order quantity I get over 66 million per month...which is absolutely incorrect.   

Will someone please help?  

Future Orders quantity for October are correctly loaded into Power BI Desktop, and should total 830,000.

Measure: 

FTR QTY = SUMX(VALUES('FUTURE ORDERS'[Product Number]),CALCULATE(SUM('FUTURE ORDERS'[Future Quantity])))

Result using data Model: Many-to-Many Relationship between received pivot (historical orders) and Future Orders.

Visual - Results in Exploded Quantity:

Example with Product Number Bridge table:

Visual Results in Exploded and incorrect quantity with Bridge Table:

Please help!  I must figure out what I am doing wrong it seems to be tied to the data model.

Thank you

[v-xiaoyan-msft]

Hi @JaclynPugh2022 ,

Is the product number in your Matrix in your bridge table, if yes, please check if this below meets your needs.

FTR QTY =
CALCULATE (
    SUM ( 'FUTURE ORDERS'[Future Quantity] ),
    FILTER ( 'FUTURE ORDERS', 'FUTURE ORDERS'[Product Number] )
        = SELECTEDVALUE ( 'All Products Union'[Product Number] )
)

Best Regards,
Community Support Team _ Caitlyn

[JaclynPugh2022]

Hi Catilyn,

I tested your recommendation and recieved this error, " FILTER has been used in a True/False expression that is used as a table filter expression. This is not allowed"

[JaclynPugh2022]

@Community Support Team _ Caitlyn

Can you or somone please help me out from here?  Any other ideas?  I need to complete this for my employer so your help is really important.

Thank you