Power pivot, Joining two tables from different databases. Duplicate values, Sum of value, Dates

[Davelockhart]

Hi there,

I'm new to Power Pivot and would appreciate some advice.


Simplified example:
I have extracted data from two databases, Inventory and Sales. Sales being Quantity and Turnover.

I want to analyze how much I have sold of my inventory within a given time range and show the turnover for the sale.

My sales data is listed with sales dates. Sales come from multiple countries, I have therefore included the countries in my table since I want my Turnover in one currency. It enables me to multiply turnover with the corresponding exchange rate giving me a single currency turnover column.

Many of the item numbers/color combinations are shown multiple times due to different sales dates and different countries. Instead of using the dates, I have created an IF formula to say Yes if the sales date is before the cut-off date.


I can’t create a relationship between the two tables since there is duplicate values in my Sales table (Table2). How do I join the data in a pivot?

Illustration:


[TABLE="width: 330"]
<tbody>[TR]
[TD="width: 179, bgcolor: #DDD9C4, colspan: 3"]Table1 (Query1)[/TD]
[TD="width: 117, bgcolor: #F2F2F2"][/TD]
[TD="width: 142, bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Number[/TD]
[TD="bgcolor: white"]Color[/TD]
[TD="bgcolor: white"]Units[/TD]
[TD="bgcolor: #F2F2F2"]Units from Table2[/TD]
[TD="bgcolor: #F2F2F2"]Turnover from Table2[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]101[/TD]
[TD="bgcolor: white"]Black[/TD]
[TD="bgcolor: white"]100[/TD]
[TD="bgcolor: #F2F2F2"]29[/TD]
[TD="bgcolor: #F2F2F2"]700[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]101[/TD]
[TD="bgcolor: white"]White[/TD]
[TD="bgcolor: white"]210[/TD]
[TD="bgcolor: #F2F2F2"]78[/TD]
[TD="bgcolor: #F2F2F2"]7000[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]102[/TD]
[TD="bgcolor: white"]Black[/TD]
[TD="bgcolor: white"]150[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: white"]102[/TD]
[TD="bgcolor: white"]White[/TD]
[TD="bgcolor: white"]95[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
[TR]
[TD="bgcolor: white"]102[/TD]
[TD="bgcolor: white"]Red[/TD]
[TD="bgcolor: white"]57[/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[TD="bgcolor: #F2F2F2"][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 303"]
<tbody>[TR]
[TD="width: 404, bgcolor: #DDD9C4, colspan: 5"]Table2 (Query2)[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Number[/TD]
[TD="bgcolor: white"]Color[/TD]
[TD="bgcolor: white"]Units[/TD]
[TD="bgcolor: white"]Turnover[/TD]
[TD="bgcolor: white"]Sold within time range[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]101[/TD]
[TD="bgcolor: white"]Black[/TD]
[TD="bgcolor: white"]2[/TD]
[TD="bgcolor: white"]100[/TD]
[TD="bgcolor: white"]Yes[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]101[/TD]
[TD="bgcolor: white"]Black[/TD]
[TD="bgcolor: white"]4[/TD]
[TD="bgcolor: white"]500[/TD]
[TD="bgcolor: white"]Yes[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]101[/TD]
[TD="bgcolor: white"]Black[/TD]
[TD="bgcolor: white"]10[/TD]
[TD="bgcolor: white"]25[/TD]
[TD="bgcolor: white"]No[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]101[/TD]
[TD="bgcolor: white"]White[/TD]
[TD="bgcolor: transparent"]55[/TD]
[TD="bgcolor: transparent"]5000[/TD]
[TD="bgcolor: white"]Yes[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]101[/TD]
[TD="bgcolor: white"]Black[/TD]
[TD="bgcolor: white"]23[/TD]
[TD="bgcolor: white"]100[/TD]
[TD="bgcolor: white"]Yes[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]102[/TD]
[TD="bgcolor: white"]Red[/TD]
[TD="bgcolor: white"]100[/TD]
[TD="bgcolor: white"]200[/TD]
[TD="bgcolor: white"]Yes[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]101[/TD]
[TD="bgcolor: white"]White[/TD]
[TD="bgcolor: transparent"]23[/TD]
[TD="bgcolor: transparent"]2000[/TD]
[TD="bgcolor: white"]Yes[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]102[/TD]
[TD="bgcolor: white"]Red[/TD]
[TD="bgcolor: white"]10[/TD]
[TD="bgcolor: white"]10[/TD]
[TD="bgcolor: white"]Yes[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance
/Dave

 

[RoryA]

Based on that sample, I'd say you need to create key columns in each table that simply concatenate the Number and Color columns.