How to create a matrix of two related tables using calculated subtotals

[pbi_J]

Hey,

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:
DIM_Process
Gross sales

Process 1

Process 2

Process 3
Net Sales
Process 4
Gross Margin
Operating Profit
 

DIM_Costtype

Gross sales

costtype 1

costtype 2

Net Sales

costtype 3
costtype 4

Gross Margin
Costtype 5
Operating Profit

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
Process
Gross sales		X
Process 1			X			X
Process 2				X			X
Process 3			X				X
Net Sales					X
Process 4
Gross Margin								X
Process 5						X
Operating Profit									X
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.

[v-yulgu-msft]

Hi @pbi_J,

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.

Regards,

Yuliana Gu