Summarizing amount from two tables that do not have uniqe primary keys

[Higgs11]

Hello all, I need to summarize the Asset Cost for each project number using two data sets. the data sets have multiple rows for each project costs.

The "main Table" has the "Project Numbers". and can show up on multiple rows.

The "Table 2" also has "Project Numbers" and has the "Asset Costs" needed to summarize. This table also has multiple rows for "Project Numbers"

I am thinking of using a PBI Matrix table and dropping the "Project Numbers" in "Rows" and then dropping the DAX Measure created here in "Values.

[v-frfei-msft · Accepted solution]

Hi @Higgs11 ，

Does that meet your requirement?

Table 2 = 
VAR a =
    ADDCOLUMNS (
        SUMMARIZE (
            'Main table',
            'Main table'[Project ID],
            'Main table'[Project Number]
        ),
        "id", 'Main table'[Project Number]
    )
RETURN
    ADDCOLUMNS (
        a,
        "c", CALCULATE (
            SUM ( 'Table'[Asset Cost] ),
            FILTER ( 'Table', 'Table'[Project No] = [id] )
        )
    )