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

Postby erdmensch » 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
Attachments
weeksdemand.xlsx
File as described.
(10.99 KiB) Downloaded 9 times

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

Re: Combining Demand table with date table

Postby MattAllington » 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

Load both of your tables to Power Pivot. The second table is your lookup 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 Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

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

Re: Combining Demand table with date table

Postby erdmensch » 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


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests