Help in consolidating data

[mbacicci]

Hi, I would need some help on the below.

I have a PBI report connected to the Database and showing invoicing data. The tables are: -invoices -products -customers.

Everything is linked and working fine. Now I need to integrate the report with other invoicing data from a new portfolio but I'm facing some issues because not all product and customer IDs are in the database.

As a consequence, when I build the relationship between the tables I get lower values.

What would you suggest in this case?

I'm already using the below calculated measures to SUM the total of invoiced amounts from the Database (which has two different invoicing tables).

Total Invoice Amount =
SUMX(
SUMMARIZE(
'Fact_Invoice',
'Fact_Invoice'[invoice_id],  
"Amount_EUR", SUM('Fact_Invoice'[Amount_in_EUR])
),
[Amount_EUR]
) +
SUMX(
SUMMARIZE(
'Fact_Invoice_SAP',
'Fact_Invoice_SAP'[invoice_id],
"Amount_EUR",SUM('Fact_Invoice_SAP'[Amount_in_EUR])
),
[Amount_EUR]
)

[mh2587]

//Use LEFT OUTER JOINS in PQ to just get the matching data and try the following measure
Total Invoice Amount =
SUMX(
   SUMMARIZE(
      'Fact_Invoice',
      'Fact_Invoice'[invoice_id],  
      "Amount_EUR", SUM('Fact_Invoice'[Amount_in_EUR])
   ),
   IF(
      NOT(ISBLANK(RELATED('Products'[product_id]))),
      [Amount_EUR]
   )
) +
SUMX(
   SUMMARIZE(
      'Fact_Invoice_SAP',
      'Fact_Invoice_SAP'[invoice_id],
      "Amount_EUR",SUM('Fact_Invoice_SAP'[Amount_in_EUR])
   ),
   IF(
      NOT(ISBLANK(RELATED('Products'[product_id]))),
      [Amount_EUR]
   )
)

[mbacicci]

Yes sorry maybe the question was a bit confusing. This measure works well for the 2 tables because all product and customer IDs are in the database.

 The new table I'm trying to integrate contains everything in there: dates, amounts, customers and products. Customers and products are new values which are not in the database.