## Combining Demand table with date table

Anything related to PowerPivot and DAX Formuale
erdmensch
Posts: 2
Joined: Fri Oct 13, 2017 3:33 am

### Combining Demand table with date table

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
Attachments
weeksdemand.xlsx
File as described.

MattAllington
Posts: 934
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: Combining Demand table with date table

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
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

erdmensch
Posts: 2
Joined: Fri Oct 13, 2017 3:33 am

### Re: Combining Demand table with date table

Hey MattAllington,

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

Thx Erdmensch