Forum Discussion

Tuan's avatar
Tuan
Helper III
7 years ago
Solved

Summing two tables based on column

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 ...
Need Help
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Tuan
    Helper III
    7 years ago

    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's avatar
      Ashish_Mathur
      Super User
      7 years ago

      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's avatar
        Tuan
        Helper III
        7 years ago

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