Creating relationships across multiple tables

[bgane]

I just installed PowerPivot, and am learning by going through the book 'PowerPivot For the Data Analyst'. I've really enjoyed the book, but I appear to missing some simple point. I have three tables. Each of the three tables have an ID. It's the same ID in all three tables. Table one contains billing for each ID. Table two contains merchant counts for each ID. Table three contains transaction counts for each ID. Using the Create Relationship option, I related Table one to Table two using ID, and then related Table two to Table three, also using the ID. However, when I build a pivot with ID for the Row, and then Billing, Merchants, and Transactions as the calculated columns, I get Billing correctly, but Merchants and Transactions are not. I receive the message that a Relationship needs to be created (although I thought I already had above), so I choose Create, but a relationship is not found. Please help me understand what step I'm missing.

Thanks!

 

[Jacob Barnett]

Without seeing the model its difficult to pinpoint the exact problem although I suspect its a mixture of tables 1 and 3 having no relationship and that tables 2 and 3 sound they have unique IDs which means the relationships could have been created the wrong way round.

Conceptually this thing might be simpler if you have a 4th table which is just a list of unique IDs - each of the other 3 tables would be linked to this one (each of the other 3 respectively would be on the 'top half' of the relationship). When creating a pivot you would drag in the ID from this newly created 4 table.

Depending on how you pull in your source data it shouldn't be a big deal to get this table to automatically generate.

Jacob

 

[bgane]

Thanks for the reply Jacob. Below is an example of what I have. I copy and pasted each of the two columns below into PowerPivot...each one creating a different sheet (Billing, Merchants, Transactions). In other words, I copied the ID and Billing columns into PowerPivot, then copied ID and Merchants into PowerPivot, and etc. Originally, I created a relationship between Billing and Merchants (using ID) and Billing and Transactions (using ID). Using your suggestion, I created another sheet that ONLY contained the ID (Called ID). I removed the original relationships, and created a relationship from ID to Billing, ID to Merchants, and ID to Transactions, using the ID field. I'm still having the same issue when I create the Pivot table. I first choose ID from the ID sheet. Since it's numeric, it initially places it in the Values section, but I drag it to Row Labels. However, when I choose the Value field, from the Billing table, I'm told a relationship needs to be created. I choose create, but I'm told one could not be found. I though I already had the relationship using the ID field. What it appears to do is sum the Value field for ALL IDs, and then display it for EACH ID, instead of just displaying the Value for just that ID.

[TABLE="width: 540"]
<TBODY>[TR]
[TD]ID</SPAN>
[/TD]
[TD]Billing</SPAN>
[/TD]
[TD][/TD]
[TD]ID</SPAN>
[/TD]
[TD]Merchants</SPAN>
[/TD]
[TD][/TD]
[TD]ID</SPAN>
[/TD]
[TD]Transactions</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]12345</SPAN>
[/TD]
[TD="align: right"]$125</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]54321</SPAN>
[/TD]
[TD="align: right"]50</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]124578</SPAN>
[/TD]
[TD="align: right"]1,000</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]54321</SPAN>
[/TD]
[TD="align: right"]$250</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]124578</SPAN>
[/TD]
[TD="align: right"]25</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1235</SPAN>
[/TD]
[TD="align: right"]2,500</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]124578</SPAN>
[/TD]
[TD="align: right"]$100</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]12345</SPAN>
[/TD]
[TD="align: right"]100</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]54321</SPAN>
[/TD]
[TD="align: right"]500</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Jacob Barnett]

Ok - I created a quick model using the 3 tables you outlined (I expanded the billing table so the thing made more sense conceptually as that looks like a 'fact' table to me).

The model is here: https://docs.google.com/file/d/0Bz5yMU2oooW2T3o5VmdiRFRYdTQ/edit?usp=sharing

Have to confess that in its current form your data doesn't really make a great deal of sense to me - some more context about what the ID means would probably help (I thought of it as a 'Store').

Nonetheless I created the model based on the structure I mentioned above with a separate ID table that is linked to each of the other 3 tables - the PIVOT I created uses the ID from the ID table and I successfully wrote some very basic measures to return the other metrics around that ID.

Hope this helps.

Jacob

 

[bgane]

I probably have not done as thorough a job as I need in explaining. Let's ignore the 3 tables, as it's happening to me with two, as well. For my example, I receive two different reports...one covering the billing for each customer that month and one convering the number of transactions for each customer that month. Each report has a customer id, which is the unique identifier for each customer. I want to create a pivot table that has two columns for each customer...one for their billing and one for their transactions. However, what it's doing is summing up ALL the billing and transactions for each customer, and making that the value for ALL customers (see example below).

[TABLE="width: 317"]
<TBODY>[TR]
[TD]Row Labels[/TD]
[TD]Sum of Billing[/TD]
[TD]Sum of Transactions[/TD]
[/TR]
[TR]
[TD]112233</SPAN>[/TD]
[TD="align: right"]$3,500.00</SPAN>[/TD]
[TD="align: right"]170000</SPAN>[/TD]
[/TR]
[TR]
[TD]223344</SPAN>[/TD]
[TD="align: right"]$3,500.00</SPAN>[/TD]
[TD="align: right"]170000</SPAN>[/TD]
[/TR]
[TR]
[TD]334455</SPAN>[/TD]
[TD="align: right"]$3,500.00</SPAN>[/TD]
[TD="align: right"]170000</SPAN>[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]$3,500.00[/TD]
[TD="align: right"]170000[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]