Merging two unrelated tables with repeating data.

[saraelaine]

I'm having a problem creating a solution to a problem. I want to be able to report on total sales by sales lead. I have two files with different sales type (new customer/existing customer) both reports have the sales lead multiple times as the sales lead might have made multiple sales and each sale gets it's own data row complete with the value of the sales lead. I want to be able to slice my report by sales lead by both types of sale and see them combined My data sets look like Old Customer Sales Lead total sale date of sale Customer Name Tony $1,500 10/1/2017 X Susan $1,200 6/27/2016 Y Brian $1,600 12/11/2016 Z Tony $1,350 4/6/2017 A Susan $950 2/23/2017 C Brian $2,000 3/20/2015 B Cindy $4,000 10/1/2017 Z New Customer Sales Lead total sale date of sale Customer Name Tony $500 10/1/2017 1 Susan $250 6/27/2016 3 Brian $1,750 12/11/2016 2 Tony $50 4/6/2017 1 Susan $1,900 2/23/2017 2 Brian $1,900 3/20/2015 2 Cindy $100 10/1/2017 9

[v-sihou-msft · Accepted solution]

@saraelaine

In this scenario, if you just want to combine two dataset without repeating rows. You can append two queries together first, then use SUMMARIZE() function to group rows into a new table.

=SUMMARIZE(Table,Table[SalesLead],Table[CustomerName],Table[DateOfSale],"Total Sale",SUM(Table[Sale]))

Regards,

[saraelaine]

Thank you! That worked.