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 .
Thanks for posting your question. You have so many many-many relationships which are not ideal. This model makes calculations very tricky to understand. Is there a way you can simplify relationships? For example, why can’t you have unique CustomerID is x3 customer. Reason is that all your relationships are many-many.
Hi @hafizsultan,
Thanks for pointing it out. I have corrected the relationship between master and transactionsTest_Sales.pbix (1.9 MB) tables. Uploading the latest pbix file with updated table relationship.
Thanks for correcting your model and now it seems a better model. However, due to changes in column structure/data types, your measures are not working when I open your file. Can you please fix these minor issues?
Thanks for sending me the file. Unfortunately, relationships in your model are not working as intended. You need to build with slight variation. In current setup, filter flows from EA SalesOrder to X3 Orders but it does not flow from X3 Orders to Sales table. How about you elminate EA SalesOrder by bringing its columns to X3 Orders etc? I believe many-to-many will work fine if it is an active relationship in this case.
Yes, 100% agreed and many-to-many should be avoided if possible. Ideal model is star-schema but in this case, I am not sure how Seema can avoid many-to-many(may be by bridge table.)
No worries. If for some reason you are unable to create data model, please send through your sample excel files and I can try to create simple data model.
I’ve been following this thread out of interest, and hope you don’t mind if I chime in. I completely agree with @hafizsultan’s recommendation to merge the EA Sales Order and X3 tables.
In addition, I think you can similarly simplify the X3 Customers and the EA Customers Table down into one.
Once you’ve done that, there are three possible options I see for resolving the many-to-many relationship:
use a bridge table as previously suggested
use a virtual relationship via TREATAS
denormalize the sales and order tables down into one combined fact table
I think once the model is simplified, evaluating these three options to determine the best one will be easier.
Hi @Seema, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Thanks for your valuable inputs.
As suggested I got the Charge, remake and showroom in X3 salesorder with lookup. Its just I am not able o make active relationship between x3 salesorder and x3 sales. I tried with a bridge table also. Its the calculation which is not correct. I will email the updated pbix file shortly.
Another problem which I can think is date filters. I am comparing sales(invoice) discounts with order discounts. Sales is done March and order is done in Dec’2020 in one of the test case which I am checking.
Hi @Seema, we’ve noticed that no response has been received from you since the 15th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @Seema, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
