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

     

4 Replies

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