(updated ) - displaying data from two fact tables in a matrix

[arunbyc]

I am still new to powerBI and am struggling with the following and need help solve this:

I have to display the details of projects in a matrix. The details for each project come from two tables : Matrials and Labor.

Materials. DB:                       Labor.DB

id                                          id

project_id                             project_id

material_id                            employee_id

quantity                                hours

Amount                                 Amount

Materials Db is linked to Materials dimension table and Labor.DB is linked to Employee.Db.

In addition to these two tables, I have a merged table that gives the combined data

I am trying to display the matrix as follows:

                                        Material cost                 Labor Cost

Project                             Quantity    Amount        Hours     Amount

==============================================

Project 1     Total              9999         8888             7777          6666

       Material 1                   5555          2222             0              0 

       Material 2                  4444          6666              0              0

       Employee 1                 0                 0                6666          3333

       Employee 2                 0                 0                 1111         3333

Project 2     Total

etc

If I had to display only the totals for each project, I could display from the merged table as usual, but the issue arises with details because I could only have materials under projects and when I insert LEmployees as the third row, they become the sub-category of Materials.

I could have done a union and put both material Ids and Labor_Ids in one column if the Ids are always distinct from each other. but they are not. So I would not be able to this column to the respective dimension tables.

I tried to see if I could use a measure to accomplish this but was unable to. Would appreciate your help.

Clarification added after some initial replies:

My issue is to get both materials and labor ids and related dimension fields under the project even when they have same ids and not with how to get totals of quantity and amount, which the matrix should do automatically. My combined (merged) table looks like this (if that can be used instead of two separate tables):

id  project_id     type      material_id    Qty   Amount    Employee_id    Hours   Amount

1   12345           Material      123          5555   2222          NULL              0            0

2    23455          Labor            NULL        0        0               123               6666      3333

etc

I tried to see if I could use some kind of measure that checks if the current record has type"material" and fetches the related dimension record "Material 1" for the material id 123 puts the material name from the material dimention as the row with all its quantity details,   and similarly, if the type is "labor", fetches the correct employee for the 123 in the employee_id and displays employee name as a row.

Thanks

AR

[Kedar_Pande · Accepted solution]

Create a Unified Dimension Table:

Combined = 
UNION(
SELECTCOLUMNS(Materials, 
"ID", Materials[material_id], 
"Type", "Material", 
"ProjectID", Materials[project_id],
"Quantity", Materials[quantity], 
"Amount", Materials[Amount]
),
SELECTCOLUMNS(Labor, 
"ID", Labor[employee_id], 
"Type", "Employee", 
"ProjectID", Labor[project_id],
"Hours", Labor[hours], 
"Amount", Labor[Amount]
)
)

Make sure that the Projects table has relationships with both the Materials dimension and the Employees dimension based on the relevant keys.

Create Measures:

Total Quantity =
SUMX(
FILTER(Combined, Combined[Type] = "Material"),
Combined[Quantity]
)

Total Labor Hours =
SUMX(
FILTER(Combined, Combined[Type] = "Employee"),
Combined[Hours]
)

Total Material Amount =
SUMX(
FILTER(Combined, Combined[Type] = "Material"),
Combined[Amount]
)

Total Labor Amount = 
SUMX(
FILTER(Combined, Combined[Type] = "Employee"), 
Combined[Amount]
)