Dax calculation- help needed

[mb0307]

Hi Team,

I have scenario…

Orders are raised in the 1st ERP system that is EA, then pulled to second ERP system X3 after that sales invoices are raised in x3.

We want to match the discounts and charges which are checked at EA sales order time with invoice discounts, charges etc. At EA Sales Order we have boolean flag for charges, remakes and showroom.

We have to reversal check. I know the correct discount and charge from invoice. But when I am calculating the discount then its not calculating correctly because of Joins.

Relation between EA salesorder and X3 orders is with OrderID, and x3 order and x3 sales with Document number and last invoice number. But not a active relationship. I have used USERELATIONSHIP then also result is not correct.

I tried making a summary table but when I join them I get error of circular dependency.

Can you please suggest , how can I get the correct result of measures Discounts, Remake and showroom. I have attached the test PBIX with data .https://gofile.io/d/akT3eH/edit 

Test case, Correct amount of discount for 2021 is -6163, for customer 148099

[lbendlin]

The data model isn't too bad

It looks like the EACustomer table can be eliminated, and it looks like you are missing the "Sales Orders"  dimension.  Once you add that you can remove the inactive relationships between the fact tables, and your measures will start working.

Please read up on the usage of TREATAS(), it will come in very handy here.