Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |