How to compare 2 pivot tables in a single chart?

Hi,

Hope all are well.

Need a help.

In attached file, sheet 2 contains 2 pivot tables - No. of batches Vs No. of tickets.

I would like to compare both tables in a single chart.

Ex. In month June, A produced 4 batches and raised 4 tickets. The comparison should reflect in a single chart. Similarly for others.

Can someone please help?

In order to get two values into the same pivot chart, the two values need to be in the same pivot table.
Since the two values come from different data sources, this requires some data modeling.

I recommend doing some research on power query and power pivot, and using these to create your data model, and then inserting a pivot table from the data model, then a chart to the pivot table.

How I would do this would be to load your two data sources into Power Query from a table and then Close & Load each as a connection, making sure to add both into the data model.
These two will be your fact tables.

You will need to create two dimension tables to create a relationship to your fact tables, one for month and one for site.
I used Power Query to do that in your example.

Then create the necessary relationships in the data model and insert a pivot table from the data model.

You didn't mention what you wanted the chart to look like, so I just inserted a simple column chart.

@63falcondude

Many thanks for your help.

The pivot table and chart you chose is definitely the one i want.

However, i need this data in my original file but I didn't understand how to do it

Can you please guide?

I briefly explained exactly how I did it in post #2.

It requires knowing how to use Power Query and understanding data modeling, which requires more time to learn than I can give you in a forum post.

You can see the Power Query steps that were taken by going to Data > Queries & Connection > then double clicking on any of the queries to open up PQ.

@63falcondude

Many thanks for your help.

Indeed it requires power pivot and power query knowledge. I tried doing by going through some posts but couldnt succeed.

Do you mind combining the 2 pivot table in the attached file please

Hi all.

Can the 2 pivot tables in file in my previous post be combined please (same as done in post # 2)

Please see attached.

My apologies. I have reuploaded now.

I tried creating the relationship but it leads to error. Sorry naïve at this. Can someone please help?

@JeteMc

Thanks for the response.

Unfortunately, slicer still doesn't work properly.

For example, when CMO is selected under slicer "Pack Owner", it also shows other data.

Also, i keep getting popup as shown in pic.

Just to clarify, the common columns to be taken from both sheets are as below.


Sheet Batches = Columns I, J, K

Sheet Tickets - Columns D, T, U