Combining Demand table with date table

Anything related to PowerPivot and DAX Formuale
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
File as described.
(10.99 KiB) Downloaded 78 times

Posts: 983
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

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 Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

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