How to support link tables in PowerPivot

[scooper43]

Hi,

I'm new to powerpivot and have the following problem.

I have products, customers, strategies, opportunities and strategic investments as follows:

Products
A
B
C
D


Customers
W
X
Y
Z

I have opportunities to sell the same product to many customers and some product to no customer so I create a link table as follows

Opportunity Product Customer
Q A W
R A X
S B X

I also have stategies

Strategy
G
H
I
J

and Strategic Investment

Strat Invets Product Strategy
L A G
M A H
N D H


So, that's all good.

Now I want a pivot table of all products and the associated customers, opportunities, strategies and strategic invetstments...

Product Customer Opportunity Strategy Strategic Investment
A W Q
A G L
A H M
A X R
B X S
C
D H N


My challenge is how to do this in powerpivot.

When I first tried just putting all the attributs of the data model into the row lables I go every customer for every product and every opporutnity for every customer etc. Didn't work.

Then I read up on Related so I added new columns on the opportunity table to find the related customer and related product but then got the right table but only where there was an active opportunity... there are many products I want to see that don't have opportunities (e.g. C and D) and I want to link in the strategic stuff - G, H, L, M, N).

I hope this makes sense,,, I'm stumped. Any help is appreciated.

Steve

 

[ruve1k]

Are you going to have any measures or do you just want to see the grouping of the dimensions?

Also, it would be helpful to use the HTML Maker when pasting tabular data into forum posts.

 

[scooper43]

sorry on the presentation -- will use HLTM maker going forward.

No measures. I suppose I could create one and then hide the row but hoping there is more elegant way. Really not a pivot table just trying to display the information.

thanks