Basic Power Pivot

[GorD]

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

 

[Matt Allington]

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]

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