simple question on merging powerpivot tables

[scooper43]

Hi,

I have been struggling with this for days so I'm hoping someone can help...

I have 2 tables; products and customers. some products go to many customers and some to none so I've created one more table to link products with customers -- basically just a key and a pointer to each table - this table is called prod2cust.

Now I want to create an output table showing all products and the customers for each... I cannot do this

I create my associations and then create a flat pivot. If I simply put in the product name and customer name in the pivot, I get all customers for every product - wrong. Then I tried to denormalize the tables by using the RELATED command and added two new calculated columns on my prod2cust table (one with product name and one with customer name.)

Then when I add only fields from prod2cust into the pivot, it works, except of course, I dont see any of the products that have no customer...

can anyone help?

Steve

 

[ruve1k]

Try putting in Product from the Products table and Customers from the Prod2Cust table.
This assumes that you have a relationship from Prod2Cust[Product] to Products[Product].

 

[scooper43]

I'm definately having a problem, not sure why -- thanks for your help. Here's what I have done... First, I setup these three tables...

Excel 2010
	A
1	Products
2	A
3	B
4	C
5	D
6	E
Sheet1

Excel 2010
	D
1	Customers
2	X
3	Y
4	Z
Sheet1

Excel 2010
	G	H	I
1	Products2Customers	Product	Customer
2	Q	A	X
3	R	B	X
4	S	C	X
5	T	B	Y
6	U	E	Z
Sheet1

Then I setup relationships so that the Products2Customers table points to both the products and customers tables.

Then I created a pivot with Products from the Products table and Customers from the Products2Customers table. Here's what I get.

Excel 2010
	B	C
9	Products	Customer
10	A	X
11	A	Y
12	A	Z
13	A Total
14	B	X
15	B	Y
16	B	Z
17	B Total
18	C	X
19	C	Y
20	C	Z
21	C Total
22	D	X
23	D	Y
24	D	Z
25	D Total
26	E	X
27	E	Y
28	E	Z
29	E Total
Sheet1

Its like the relationships are not being setup properly. Any thoughts? Do I need to re-install?

Steve

 

[ruve1k]

You need to add a measure into the values area for the context to kick in.
See this related blog post:
http://blogs.msdn.com/b/johndesch/a...sure-is-added-to-the-powerpivot-workbook.aspx

 

[scooper43]

Thanks,,, so I added a measure:
Number of Deals:=COUNTA(Table3[Products2Customers])

and I get

Excel 2010
	B	C	D
9	Products	Customer	Number of Deals
10	A	X	1
11	B	X	1
12	B	Y	1
13	C	X	1
14	E	Z	1
Sheet1

which is expected behaviour. But when I choose "Show data with no items on rows", I'm back to...

Excel 2010
	B	C	D
9	Products	Customer	Number of Deals
10	A	X	1
11	A	Y
12	A	Z
13	B	X	1
14	B	Y	1
15	B	Z
16	C	X	1
17	C	Y
18	C	Z
19	D	X
20	D	Y
21	D	Z
22	E	X
23	E	Y
24	E	Z	1
Sheet1

This certainly is strange behaviour. So if my measure resulted in no value I only have the choice of not seeing the row or seeing all the rows that are incorrect. I cannot understand this...

thanks for the help
Steve

 

[ruve1k]

You can use the following measure to get the output you want. (You get a row for product D.)

=IF(ISFILTERED(Prod2Cust[Customer]) && COUNTA(Prod2Cust[Customer])=0, BLANK(),1)

<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th>
</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Products</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Customer</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">M3</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">A</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">X</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">X</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">1</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">B</td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Y</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">C</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">X</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">D</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">E</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Z</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1
</td></tr></tbody></table>

 

[scooper43]

Thankyou so much -- worked perfectly.

Steve