Page 1 of 1

### Combining Demand table with date table

Posted: Fri Oct 13, 2017 3:47 am
Hy everyone,

new to PowerPivot and a litte curious.
I have two tables and want to make a pivot out of the combined results.

The first table is a matrix with parts numbers as rows and order numbers as columns.
The actual demand is in the matrix. Like 4 pieces of part 333 in ordernumber 12.

The second table hast the order numbers from table 1 and the date of the orders.
Like ordernumber 12 is due in calendarweek 47.

Now I want to combine this data in a Pivottable.
So I can see how much demand for a part number is due in a specific calendar week.

The question is if Powerpivot is the right tool to look into in this case?

I attached a excel showing the case in detail.
demand per order is table1 as used above, order date is table 2 as used above.
Table aggregated demand shows how I try to have it.

Best greetings
Erdmensch

### Re: Combining Demand table with date table

Posted: Fri Oct 13, 2017 7:19 am
Yes, Power Pivot is a great tool for this

The first thing you should do is unpivot your first table. You need a table shape like this
Part No, Order No, Qty
abc, 123,10
def, 123,3
etc

This will be your data table

Join the tables in the relationship view on the Order Number column. The lookup table is on the one side of the relationship and the data table is on the many side (all relationships must be 1 to many).

Place calendar week on columns in a new pivot
place part number on rows.
write a measure Total Demand = SUM(DataTableName[Order Qty Column])
Put that measure in values

### Re: Combining Demand table with date table

Posted: Sat Oct 14, 2017 4:01 am
Hey MattAllington,

that totally did the trick! Thank you.
Guess I have to look into PowerPivot more often.

Thx Erdmensch