Sales and Purchase in the same table

[pbi1908]

Hi everyone, 

I have a table which contains Sales and Purchase and all the information gathered together. 

The sales are conntecte with Purchase since is a trading table so i have a Sale (ID 11) and under this Sale i have some Purchases (11-1,11-2,11-2 etc.).

So in the same table i have all the information such as Sale Price, Purchase Price, Sale Quantity ,Purchase Quantity, the Quality of the product, under same columns. 

I was thinking how i will handle this situation in PBI and i was thinking to seperate them as two Fact Tables (Sales Fact, Purchase Fact) and use all the metrics from there and keep the unified table as a Dimension table and keep only dimension there. 

So when i want to make these two tables interactive betwen each other to use the Dim table. 

I am not so expirienced with Data modeling so can you please tell me if i am in the correct way or if you can suggest something else ?

[daXtreme]

The simple and correct answer to your question is this. Proper data modeling calls for conformed dimensions and fact tables. Each separate process has to have its own fact table. So, in your case you want to have 2 different tables connected by conformed dimensions. Easy as that.

[pbi1908]

@lbendlin 

Yes now is working thanks 🙂

Just a last quesiton, do you think that to use as a bridge table the unified one instead of the Calendar is a good idea?

[lbendlin]

I would not recommend that. Usually bridge tables are an indicator for deeper problems.

Learn to use TREATAS() instead.

[pbi1908]

@lbendlin In that case i need a Column with TREATAS() function in Purchase table, wich will filter the table when i click to the related sale. Something like this i guess.

[lbendlin]

Are these events (sale and purchase) happening at the same time?  Is the Sale Date different than the Purchase Date?  In that case you would be better off with separate fact tables.

[pbi1908]

@lbendlin Thanks for your answer. 

  Yes exactly the Dates are different. Imagine that I am the third party, I create a Sale of 100 products today, this sales could take 1 month until i consider it as closed.

So I start to purchase the 100 products from different suppliers 10 from one 20 from another etc. so some of purchases are considered as closed and some else no. 

[FreemanZ]

As i understand, in your case, the Purchase Order and Sales Order are in a many to many relationship. 

In cases like this, normally it is suggested to create a dimensional table as a bridge for comparison analysis. For example, the Date table is the most typical bridge table.

In a nutshell, you are on the right way. Good luck. 

[pbi1908]

@FreemanZ 

Take a look in the PBI, i would like when i click to a sale in the right table to see only the purchases related to this table. If i click in a purchase in the left table to see only the related sale. 

Also i would like when i choose the slicer above after a specific date for example to see only the Sales and Purchases which are created after that specific date. 

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

[lbendlin]

If i click in a purchase in the left table to see only the related sale. 

One way to do that is to maniupulate the search direction in your data model (making it bidirectional from one of the fact tables). 

A better way is to create measures and use them as filters for the visual.

The worst option would be to link the fact tables.  Don't do that.

[pbi1908]

Yes i found this, i still have the issue with the date slicer.

[lbendlin · Accepted solution]

Have you created the measure filter as I recommended?