Relational database using pivot tables

Question

Hi All,

I've been using pivot tables for a while now to combine data from several database with great results. However one issue keep plaguing me when I with use the data model to combine data into a single pivot table as a relational database (instead of using vlookup() or index() and match() functions) and not just to necessarily use the pivot table data analysis features.

Here is a simple example. I have two tables, one contains the first name and last name of three individual and the second table has the last name, **** and age of the same individuals:

Table 1:

First	Last
Tom	Smith
Dick	Jones
Jane	Brown

Table 2:

Last	****	Age
Smith	Male	20
Jones	Male	21
Brown	Female	22

If I insert a pivot table using these two tables with first name, last name, **** and age the result looks like this:

First	Last	****	Age
Dick	Jones	Female	22
		Male	20
			21
Jane	Brown	Female	22
		Male	20
			21
Tom	Smith	Female	22
		Male	20
			21

Only if I add the age as a calculated field (for example sum) do I get the correct result, namely:

First	Last	****	Sum of Age
Dick	Jones	Male	21
Jane	Brown	Female	22
Tom	Smith	Male	20

If anyone can shed some light on why this is happening I'd be very interested.

Answer 1

Hi,

This can be solved without writing a calculated field formula in the PowerPivot (though I would prefer the calculated formula route).

In the PowerPivot window (the Data Model), establish a relationship from the Last column of table1 (named Participants) to the Last column in the second table (named details).  Once that is done, write a calculated column formula in the Participants table to bring over Gender from the Details table

=RELATED(details[Gender])

Give this column an appropriate title

Write another calculated column formula in the Participants table to bring over Age from the Details table

=RELATED(details[Age])

Give this column an appropriate title

Now build you table from the Participants Table.

Hope this helps.

Answer 2

Have you set up relationships between the two tables? Without doing so, Excel doesn't know you want to link Last in the two tables.

Answer 3

Hello,

Welcome to Microsoft Community and thank you for posting your query.

Since you are using Pivot table, you may post your query in Excel IT Pro using the forum link below for better suggestion.

https://social.technet.microsoft.com/Forums/office/en-us/home?forum=excel

Hope this information is helpful. Please feel free to reply in case you face any other issues with Office in future.

Thank you.