Forum Discussion
Matrix table with multiple columns as rows and measures doing calculations
I have a table that contains cost by region. The cause of the costs are indicated by flags of multiple columns indicating if a process was done in the record. This is an example table of it:
| Region | Cost | Construct 1 | Construct 2 | Construct 3 | Construct 4 |
| North | 1000 | Y | N | N | N |
| North | 1500 | N | Y | N | Y |
| North | 1250 | Y | N | Y | N |
| South | 2000 | N | Y | Y | Y |
| South | 3000 | N | Y | Y | N |
| South | 1500 | N | N | Y | Y |
| South | 500 | N | N | N | Y |
| East | 1750 | Y | N | N | Y |
| East | 750 | N | N | Y | N |
| East | 1000 | N | Y | N | N |
| East | 1500 | Y | N | Y | N |
| West | 1000 | N | Y | N | N |
| West | 2500 | Y | N | Y | Y |
| West | 2000 | N | N | Y | Y |
The first row shows a cost of 1000 that used the process construct 1 in the North region, indicated by the "Y". The second row is a cost of 1500 that used construct 2 and construct 4. As such, I am unable to combine the columns into one as records can have multiple constructs. Additionally, I am not able to unpivot the data, as the real table uses unique IDs for each record and they can not be duplicated in order to maintain a one to many relationship.
The goal is to create a matrix table where each contruct process is listed as rows, and measures can be used to do simple calculations (sum, average, etc.). The table would ideally look something like this (sum of cost example, which yes the total when added individually together is inflated):
| North | South | East | West | |
| Construct_1 | 2,250 | - | 3,250 | 2,500 |
| Construct 2 | 1,500 | 5,000 | 1,000 | 1,000 |
| Construct 3 | 1,250 | 6,500 | 2,250 | 4,500 |
| Construct 4 | 1,500 | 4,000 | 1,750 | 4,500 |
The values under each of the construct columns can be changed (0 / 1, actual full names of the columns). Any help on this, even if explaining why this is not possible, would be really helpful. Thank you!
Hi Nobuko42
Thanks for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank parry2k and Ashish_Mathur , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum.
We’ve used a combination of a clean data model and a DAX measure to produce the correct Construct-by-Region results. By separating customers, costs, and constructs into proper tables, the report now calculates each construct’s total cost accurately, even when customers have multiple constructs.
Please refer to the attached .pbix file for a working example of the full solution.
I hope the information provided helps you to fix the issue.If it is still outstanding, please share any additional information so we can assist you further.
Best Regards,
Community Support Team
7 Replies
- parry2k
Super User
Nobuko42, the shape of your table is not as per the best practices. As you mentioned, you cannot unpivot, but that is the best approach; otherwise, you will be noodling around the solution. Even though you have mentioned it will break the relationship, you have to think long-term and plan for the data model, which is scalable and doesn't require a lot of work to achieve a simple goal.
I hope it helps.
What could be done then if the table is unpovited resulting in a many to many relationship? The data model structure consists of a cost table and customer records table. A customer typically has multiple costs associated to them in a given period, thus there is a one to many relationship between the two tables using a customer ID. If I were to unpovit the customer table, is there a way to address the many to many relationship created?
- Ashish_Mathur
Hi,
Selete the Many to Many relationship. Create a table with unique customer ID's. Create a Many to One relationship from the fact tables to this unique customer table.
v-karpurapudCommunity Support
Hi Nobuko42
Thanks for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank parry2k and Ashish_Mathur , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum.
We’ve used a combination of a clean data model and a DAX measure to produce the correct Construct-by-Region results. By separating customers, costs, and constructs into proper tables, the report now calculates each construct’s total cost accurately, even when customers have multiple constructs.
Please refer to the attached .pbix file for a working example of the full solution.
I hope the information provided helps you to fix the issue.If it is still outstanding, please share any additional information so we can assist you further.
Best Regards,
Community Support Team- v-karpurapud
Hi Nobuko42
We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank You.
