Power Pivot not linking

[kamper59]

Hi,


I have two tables with information of: Inventory, Sales. I have created a link between Inventory and Sales by Item ID (1 to Many, unique in Inventory and Multiple in Sales).

However, when I try to mix information from Inventory to Sales (for example: Stock Availability) what I see is all the options (see picture). What I need is to see ONLY the availability of the corresponding part (see picture).

What I get:

What I need:

 

[kamper59]

Hi, Pictures:
1:

2:

3:

 

[kamper59]

Does anyone know how to solve this?

 

[kamper59]

No ideas?

 

[chrisco]

No ideas?

I'm not any kind of expert, but a few things I've encountered:

Make sure the data type in the Power Pivot tables are the same (text vs number vs date, etc)
Also, sometimes you need to have a value in the value field for the row labels to make sense.


You may have already tried these, but hopefully they help.

 

[Matt Allington]

Hi,

However, when I try to mix information from Inventory to Sales (for example: Stock Availability) what I see is all the options (see picture). What I need is to see ONLY the availability of the corresponding part (see picture).

filters only propagate from the 1 side of the relationship (the lookup table) to the many side of the relationship (the data table). When you put a column onto rows in a pivot, you are "filtering". So if you put Items[item number] on rows, and salesorders[item name] in values, you will correctly get the count of sales orders by item number. But if you put salesorders[item id] on rows and items[item name] in values, you will get the total number of items on every row of the pivot table. This is because filters only flow in one direction automatically.

Normally pivots aggregate data, but you don't mention how your data aggregates up or how you need the data to behave. There is a way, but I need you to be more specific about exactly what you expect. I can't actuall tell which columns come from which tables from the info you have provided.

If you want to quickly become skilled at power pivot, you may like to consider reading my book. Http://Xbi.com.au/learndax.