Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Tuan
Helper III
Helper III

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi @Tuan ,

 

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

 

Regards,

Xiaoxin Sheng

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.

 

image.pngimage.png

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors