Costs/sales in different fact tables - Calculate a margin?

rasmusilestedt — Sat, 16 Nov 2024 06:34:44 GMT

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!

Re: Costs/sales in different fact tables - Calculate a margin?

rasmusilestedt — Fri, 15 Feb 2019 12:50:39 GMT

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).

topic Costs/sales in different fact tables - Calculate a margin? in App Development

Costs/sales in different fact tables - Calculate a margin?

Re: Costs/sales in different fact tables - Calculate a margin?