cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbi_J Visitor
Visitor

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

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:

 CosttypeGross salesCosttype 1Costtype 2Net SalesCosttype 3Costtype 4Gross MarginOperating ProfitTotal
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.

 

 

1 REPLY 1
Community Support Team
Community Support Team

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

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.