I have 2 DIM tables that I want to combine in a matrix where I would put the value from the FACT table in.
The tables look like this:
Each costtype can have one or many processes.
Now, I would like to build a matrix like this:
|Costtype||Gross sales||Costtype 1||Costtype 2||Net Sales||Costtype 3||Costtype 4||Gross Margin||Operating Profit||Total|
*Where X is just some numbers.
Netsales, Gross Margin, and Operating Profit would be calculated subtotals. For instance Netsales: sum of gross sales - process 1,2,3.
Does anyone have any idea? Greatful for any input.
Please provide some dummy data to make the relationship between tables ro be more clear. How is your FACT table? Besides, please illustrate the desired output in Matrix with examples. I need to know how to calculate the subtotals.