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 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]
)

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • 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.