Forum Discussion

mbacicci's avatar
mbacicci
Helper I
2 years ago

Help in consolidating data

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 worki...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
mh2587's avatar
mh2587
Super User
2 years ago
//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's avatar
mbacicci
Helper I
2 years ago

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.