Basic Power Pivot

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Hi I'm trying to teach myself power query (going well) and power pivot (not so well).

This seems really basic but doesn't work as i envisaged.

I have two tables loaded to data model.

One has Assembly no, component number and qty per ( components can repeat)

The second table is a unique list of components) and price.

I relate the tables on component field and create a pivot table with assembly, component and price and expected to get the price for the part ( i can pull the price across using related, but not the point). Instead under the component i get all the prices listed. WHy?

Obviously must be a misunderstanding on my part of how this should work

Thanks for any pointers
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,247
Yes. You need to understand how power pivot works. In your example, the first table is a data table and the second table is a lookup table. A lookup table is on the one side of a one to many relationship and the data table is on the many side. The pivot table provides filtering to you data model however this natural filtering only flows in one direction, from the lookup table (one side) to the data table (many side).

You you may like to consider buying my book to help you get started - it will help you :).

Http://Xbi.com.au/learndax
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Thanks Matt, I have relationships defined between the table on the component field. Many on the Bom table to One on the Prices table.

I think i have it working ok - i was selecting assembly and component from the BOm table along with price from the Prices table. If i choose component and price from the prices table it seems to work. I had thought it wouldn't matter which table i picked the component from
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top