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

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



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

Process 1

Process 2

Process 3
Net Sales
Process 4
Gross Margin
Operating Profit


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


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



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.



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.