Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
rasmusilestedt
Contributor
Contributor

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 customerwith 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! 

 

 

 

Labels (3)
1 Reply
rasmusilestedt
Contributor
Contributor
Author

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