PowerPivot Basic Help Needed

[JNM]

Hi - am learning PowerPivot on Excel 2013 and need a little help. I have brought in three tables - Saleslines is the Fact Table, two columns SLItemNo and SLQty. The lookup tables are Inventory (INItemNo and INQty) and Packaged (PKItemNo and PKQty) both linked to the SLItemNo. When I build the pivot selecting SLItemNo on rows and try to add the PKQty, I get a popup asking to create a relationship - but I have already created the relationship. If I ignore it, I get the same PKQty value for all SLItemNos. What am I missing? I don't have an option to post attachments.

The results should look like:
SLItemNo - SLQty - PKQty - InvQty
A1 - 125 - 25 - 50
A2 - 19 - 10 - 5

I'm getting:
SLItemNo - SLQty - PKQty - InvQty
A1 - 125 - 1000 - 1000
A2 - 19 - 1000 - 1000

Thank you.

 

[scottsen]

Would help if you can post a pic of your model, but in generally it *feels* like you could use an ItemMaster table?

Relationships filter from the "1 side" (lookup) to the "many side" (fact). And they only go 1 direction. So, SLItemNo from the lookup table should properly filter facts, but not the other way around.

 

[JNM]

Thanks for the suggestion - I did try that, but it didn't work as I needed it to either. But, I do have it working!
I created two related columns in the SlsLn table - one for Pkg and one for Inv. I then created explicit values (Calculated Fields) for the sums of the three qty fields - Sls, Pkg, Inv, plus added an explicit value (Calculated Field) for count of item nos in the SlsLn table. Then divided the Pkg and Inv sums by the count and waa laaa - numbers turned out perfectly. I'll bet there is a way to do this with a Calc formula so I wouldn't need to add the "related" columns to the SlsLn table. But it works for my purposes as it is.