Appending Tables without Duplicating Values?

Power BI
1

Hello,

This is probably a basic question, but I am wanting to pull product data (such as quantity sold and product name) from a sales ledger table, but get the totals from the GL, in order to reconcile the totals back to the TB, as the sales ledger is missing some manual postings.

Both tables use the same invoice number (many to many relationship), which creates conflicts.

However, there may be multiple product lines per each invoice in the sales ledger, so if I try to append/merge using the invoice number as the commanility, there is a duplicate of values.

Is there a way around this?

Attached is a PBIX of sample data, I would like to end up with the table “2” below, but using the gross sales from the GL fact table. Thanks for your time!

image
image372Ă—516 54.6 KB

Sales and Quantity.pbix (318.2 KB)

2

Hi @Juju - Have gone through the PBIX file but the requirement is not clear. In Diagram 2, can you provide an example of what exact value will be returned. Also, some explanation on how values will be identified for Products as there is no Product information in Fact_GL table and the Invoice number is same.

Thanks
Ankit J

1 Like
3

Hi @Juju

Use this DAX code

Table 2 = 
    SUMMARIZECOLUMNS(
      'Dim_Product'[Product Name],
      "Inventory_Quantity", CALCULATE(SUM('Fact_Sales Table'[Inventory Quantity])),
      "Gross_Sales_Amount", CALCULATE(SUM('Fact_Sales Table'[Gross Sales Amount]))
    )

image

Please don’t forget create you own measure don’t work with implicit measures.

4

Thanks very much! Yes sorry i was being slack with the dummy data model! Much appreciated!

1 Like