Costs/sales in different fact tables - Calculate a margin?
Hi community!
Background:
I have two fact tables. In the first fact table, I have costs (the data field), as well as keys to dimension of what cost-type, what item-type, and what time.
In the second fact table, I have the sales (the data field). Again, I have keys to dimensions of what item-type and at what time. I also have a customer-key. However, I do not have a cost key, since it is the table of sales.
My problem is that I would like to be able to calculate a margin per customer-type. This means, that I would like to sum sales that have occured for a customer, with help of the fact that they have the item in common.
Getting the margin per item is easy, but as soon as I filter for a customer dimension, the costs and the margin turns to 0 - since the customers have no costs recorded on them. But if I select an item type, I see both sales and costs.
So I feel like there should be a way to filter out customer as well to get the sales and costs for a specific month, but perhaps I need to re-think my data model and add something else, like quantity, in order to perform a calculation that will work.
I have attached a picture of my data set (which with current set-up creates synthetic tables). I would like to hear your advice on:
1) Is this even possible to accomplish what I am looking for?
2) If possible, what would the smartest way forward be? (LinkTable or concatenate?)
Very thankful for your support or thoughts, friends!
Could a solution be to add two more fields in each fact table regarding quantity? In this way, I can get the cost per item produced as well as price paid for each customer/item transaction.
But in the next step, I do not know if it would be possible to do a set analysis and subtract price paid (item/customer) minus cost of producing (item).
