Summing two tables based on column

[Tuan]

I have two tables, Orders and Invoices. The Invoice table has both Order Number and Invoice Number.

I'm trying to get the  Sum of Sales from the Invoice Table + the sum of sales not yet invoiced using the order number column.

I'm also trying to get the Sales not yet invoiced using the same column filters.

This is what I been fiddling with currently.

Demand Sales = CALCULATE([Sales],

(CROSSFILTER(FctInvoice[ORDER_NUMBER],FctOrders[ORDER_NUMBER],Both)))

[v-shex-msft]

Hi @Tuan ,

I'd like some sample data with expected result to clarify your data structure and do test on it.

Regards,

Xiaoxin Sheng

[Tuan]

When Orders convert to Invoices the values can change. I'm trying to get the "Sales + Outstanding" value using a measure.

Here's the tables. I also put the Model, I made an inactive many-to-many  between the invoice and order to try to do what I need.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Table.Combine({Invoices, Orders}),
    Partition = Table.Group(Source, {"Sales Order"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Invoice Order", "Sales", "Index"}, {"Invoice Order", "Sales", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Partition", each ([Index] = 1)),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Invoice Order", "Sales Order", "Sales", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Invoice Order", "Invoice Number"}, {"Sales", "Sales + outstanding"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Sales + outstanding", type number}})
in
    #"Changed Type"

Hope this helps.

[Tuan]

That works and I did something similiar, trying to figure out a a dax solution instead of flattening the tables.