Combining 2 pivot tables into 1 chart

[Ramballah]

Hello everyone,

My question sounds simple but I do not know the answer.
I have 2 pivot tables and 2 charts that go with this. However I want to merge them into only 1 chart.
I have no intention of combining the raw data together because we are resetting the data from both tables weekly.
So what we want is the 2 tables and 1 chart consisting of the 2 tables. Here is my example (sorry its dutch)
Lets call the table on the left table A and the table on the right table B.
The top chart belongs to table A and the bottom chart belongs to table B.
I want to see both table A and B but only have one chart. The design for the chart im not entirely sure what looks best.
Currently im thinking about having table A go upwards in the chart and table B downwards. Or have both tables next to each other so I can see which table is higher.
I hope I didnt confuse anyone and that someone can help me with this.
Thanks in advance,
Ram

 

[GraH]

HiRamballah,

Simply combining both pivot charts can't be done. However you can create an intermediate table for the single chart and use a standard chart. Can you share the data from these pivots via XL2BB? (retyping from a picture ain't fun... )

Some tips for the pivot charts:
- reduce the noise by removing the field buttons
- when the legend has no purpose, delete it
- axis labels, set them to "low" so the labels are not crossing your chart
- dim the grid and label colour
- format the axis in K€

 

[Ramballah]

HiRamballah,

Simply combining both pivot charts can't be done. However, you can create an intermediate table for a single chart and use a standard chart. Can you share the data from these pivots via XL2BB? (retyping from a picture ain't fun... )

Some tips for the pivot charts:
- reduce the noise by removing the field buttons
- when the legend has no purpose, delete it
- axis labels set them to "low" so the labels are not crossing your chart
- dim the grid and label color
- format the axis in K€

Hello,
I am unfamiliar and probably unable to use XL2BB. So I transferred some of our raw data to an excel file and uploaded it (I hope this works for you too).
I left some columns empty due to privacy reasons.
The tabs creditor and debtor are our raw data generated by some big formulas (I only posted the values in this file).
In the end, we want to see a pivot table with the week numbers and total open amount $. But we want one chart matching the debtors and creditors.
Could you perhaps also show me how you did this for future projects?
And feel free to add in the pivot tips you gave me since I'm not entirely sure on what you meant with those (My native language is not English I am sorry)
Thank in advance,
Ram
This is the link for my Excel file uploaded on easyupload

 

[sandy666]

try Alt, D, P
add both ranges and create single Pivot then Chart

 

[sandy666]

maybe

but you need to choose what you want to show

 

[GraH]

try Alt, D, P
add both ranges and create single Pivot then Chart

TRUE, overlooked that one , however when the ranges grow/shrink it can become messy.

 

[sandy666]

TRUE, overlooked that one , however when the ranges grow/shrink it can become messy.

another solution is to use Power Query, transform data into one table then create Pivot Table (and PT chart)
or
use Power Pivot, create relationship then create flat Pivot Table (and PT chart)

neither of these solutions affect the original data

 

[Ramballah]

another solution is to use Power Query, transform data into one table then create Pivot Table (and PT chart)
or
use Power Pivot, create relationship then create flat Pivot Table (and PT chart)

neither of these solutions affect the original data

I'd love to try your solutions but I am afraid that I am not skilled enough to understand how to perform these actions.
Also let me clarify, these are basically our invoices and thus new ones get added and some get deleted every week when I refresh the data.
Could you show me how to perform these steps that you mentioned?
I should mention that I want to see the differences in the chart between my two tables. I don't want to sum them up.
If creditor week 40 = 21034$
If debitor week 40 = 42314$
I want to see them both ina chart and not week 40 = 63348$

 

[sandy666]

see this (example)

Mr Excel datasheet.xlsx

Microsoft Excel Workbook

1drv.ms

you can do what you want with Pivot or Chart
if you add or remove any data from source tables just Refresh Pivot Table then you will get "new" data

note: I changed your ranges to Excel Tables which has no effect on the data and is easier to use

 

[Ramballah]

see this (example)

Mr Excel datasheet.xlsx

Microsoft Excel Workbook

1drv.ms

you can do what you want with Pivot or Chart
if you add or remove any data from source tables just Refresh Pivot Table then you will get "new" data

note: I changed your ranges to Excel Tables which has no effect on the data and is easier to use

Yes this end result is exactly what I was looking for. Could you explain how you did this? I have more of these charts that I need to make so that be really nice! thanks already!