Joining two tables with relationship in Excel and Pivot Table inserted
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Joining two tables with relationship in Excel and Pivot Table inserted
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,
BittenApple
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,
BittenApple
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78678
- 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
I cannot make it work either. I'll be curious to see if someone else knows how to do this.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Joining two tables with relationship in Excel and Pivot Table inserted
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
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
-
- Administrator
- Posts: 78678
- 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
Did you manage to make it work?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: Joining two tables with relationship in Excel and Pivot Table inserted
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.
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Joining two tables with relationship in Excel and Pivot Table inserted
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
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
-
- Administrator
- Posts: 78678
- 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
Hi BittenApple,
I have no idea why it doesn't work, sorry.
I have no idea why it doesn't work, sorry.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: Joining two tables with relationship in Excel and Pivot Table inserted
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 ?
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 ?
-
- 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
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.
-
- 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
This blog post describes your problem exactly: https://powerpivotpro.com/2013/05/relat ... -going-on/
-
- Administrator
- Posts: 78678
- 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
Thanks, Jan Karel! Good find!
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78678
- 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
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:
I used this calculated column in the pivot table:
Here is the workbook:
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:
I used this calculated column in the pivot table:
Here is the workbook:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Joining two tables with relationship in Excel and Pivot Table inserted
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
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
-
- Administrator
- Posts: 78678
- 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
Indeed, it doesn't work by itself.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: Joining two tables with relationship in Excel and Pivot Table inserted
Unless you use the suggestion I posted.
-
- Administrator
- Posts: 78678
- 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
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.
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...
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
Hans
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: Joining two tables with relationship in Excel and Pivot Table inserted
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....
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.
-
- 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
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.
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Joining two tables with relationship in Excel and Pivot Table inserted
Thanks for clarification.
Respectfully,
BittenApple
Respectfully,
BittenApple