Any one to help me?
I took a look at this. I think the main problem you are having are the multiple one to many relationships across your tables. Shawn highlighted this above.
The blended sample you have is going to be difficult to work with because you need to have a relationship from each table to the primary source. On your sheet 3, your primary is assets, which has a relationship to customer asset allocation table, but as you see the order table does not have a relationship with assets which is why you are getting * there.
So I tried to join it. This still isn't going to be much better because of the one to many.
order id: oid-4 has a total of $63 payment amount. But we have those line ids you want to see so $63*9 = 567
You can try some hackery with some calcs to get to the order totals you expect.
For example, here if there is more than 1 order line id, I took an average, if not the sum. Order 4 now totals $63.
Then I tried to put your tables in access to see if it is any better
Looks familiar right?
So I think you are going to either need to restructure your data or use multiple worksheets to tell your story. As you can see, Tableau reproduced the same joined result as access.
I am attaching everything here.
Really really appreciate it.. and sorry for the late reply i was traveling.. I came to the same conclusion as you before i posted this.. my only question was if there is a work around this or If I have to write a sql query in the backend and use only to visualize..