Joining two tables with relationship in Excel and Pivot Table inserted

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Joining two tables with relationship in Excel and Pivot Table inserted

Post by BittenApple »

Hello team,

I tested some data and created relationship by going to data tab and relationship option. I joined two tables and then inserted a pivot table on the joined data.

The sum in pivot table is not breaking by customerId. What is the reason for it? How to take care of this issue?

Respectfully,
:thankyou: :thankyou: :thankyou: :thankyou: :thankyou:
BittenApple
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by HansV »

I cannot make it work either. I'll be curious to see if someone else knows how to do this.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by BittenApple »

Hello Hans,

Please go to this link:

https://chandoo.org/wp/introduction-to- ... tionships/
and please go to:
Creating a relationship in Excel – Step by Step tutorial

Respectfully,
BittenApple

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by HansV »

Did you manage to make it work?
Best wishes,
Hans

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by snb »

I'm not sure if this is what you are looking for....
You do not have the required permissions to view the files attached to this post.

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by BittenApple »

Hello Hans,
I couldn't make it work. I followed the instruction on the link, base on that, we need to get the sales by customer, but we can't. Why?

snb,
Thanks for the response!
Did you create a relationship between two tables?

Regards,
BitteApple

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by HansV »

Hi BittenApple,

I have no idea why it doesn't work, sorry.
Best wishes,
Hans

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by snb »

Of course.

In tab Data, connections you will find 'Query from Excel files'.

Or check the pivottable's properties: select the pivottable, Tab Pivot table tools/options/change data source/connection properties/ tab 'definition'/ commandtext.
How else could the result being obtained other than based on the relationship between table 1 and table 2 ?

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by Jan Karel Pieterse »

snb used old-style queries, which is fine but IMO this should also work using relations. I just don't understand why yet. I'm asking around to find out if anyone knows how to do this properly.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by Jan Karel Pieterse »

This blog post describes your problem exactly: https://powerpivotpro.com/2013/05/relat ... -going-on/
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by HansV »

Thanks, Jan Karel! Good find!
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by HansV »

Hi BittenApple,

See the attached workbook. I took the second suggestion from the article mentioned by Jan Karel Pieterse: in PowerPivot > Manage, I added a calculated column to tCus with formula =RELATED(tPro[Sales]); I named it Sales_from_tPro:

S3307.png

I used this calculated column in the pivot table:

S3308.png

Here is the workbook:

Relation In Excel2.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by BittenApple »

Thanks for all the responses and inputs!

From what I read and understood, we need to use Power Query to create a calculated field then insert a pivot table. So, we can't just link two tables in Excel and insert a pivot table as it is demonstrated in the link:

https://chandoo.org/wp/introduction-to- ... tionships/

Respectfully,
BittenApple

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by HansV »

Indeed, it doesn't work by itself.
Best wishes,
Hans

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by snb »

Unless you use the suggestion I posted.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by HansV »

Hi snb, your suggestion joins the tables using SQL. That works fine (of course), but it doesn't use the relationship between the tables specified by clicking Relationships in the Data tools group of the Data tab of the ribbon.
S3310.png
According to several tutorials, Excel should automatically use such a relationship when you create a pivot table based on two tables with a relationship, but it doesn't...
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by snb »

If you select the pivottable in my file, you will find all fields of both tables in the field list.
That is only possible if the relationship between both tables is being used.

See the attachment how to....
You do not have the required permissions to view the files attached to this post.

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by Jan Karel Pieterse »

Hi snb, yes it is silly that technology from 1993 actually works better for this particular situation than trying to use modern technology. You can do it, but you must use some DAX to pull it off.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: Joining two tables with relationship in Excel and Pivot Table inserted

Post by BittenApple »

Thanks for clarification.
Respectfully,
BittenApple