Pivot table question-2 data sources to be compared

[nevila]

Hi everyone,

I've got two tables with the same format (one Is the target and the other one is the actual achievements) I need to make a pivot table because I need to compare the results . Each of the tables has ~10 columns and I don't want to merge the two of them in one table.
I've tried Pivot table and PivotChart Wizard but it consolidates the data so its not what I'm searching for. Is there any solution for this?

thnx a lot

 

[Chihiro]

Upload sample workbook. With expected out come.

In most cases you will need merged table, whether it's in data model (not written to the sheet), helper column on one of the table, or summary table.

 

[nevila]

attached a sample. Actually I use the same pivot table but changing data source (both tables have the same number of rows and columns per each period compared)

 

[Chihiro]

Here's the method using PowerQuery.
Note how the data was transformed into flat table from cross tab table structure.

While cross tab table is more human readable, for data analysis, flat table structure is ideal.

Unfortunately, without PowerPivot you can't add calculated field to PivotTable generated from Data Model. So you'd need to return merged table from PowerQuery to sheet. And use that to build your PivotTable.

If you add data to Target & Achievement sheet, Merged Table and Pivot will update itself when refreshed.

See attached.

 

[nevila]

ok, I'll try to use this method.
Many thanks

 

[Ismail Hoxha]

Dear Nevila,

Use PowerPivot, if you don't have that in your Excel, you can Download that from Microsoft site.

Br

 

[Chihiro]

@Ismail Hoxha

FYI -
PowerPivot is only available to specific licensing for 2013 & 2016.

Office 2013
- ProPlus (Office 365 subscription)
- Enterprise Volume license (not E1)
- Standalone Excel

Office 2016
- ProPlus (Office 365 subscription)
- Pro SKU (retail)
- Enterprise Volume license (not E1)
- Standalone Excel

Office 2010
- Free add-in for all license

Some articles to read:
https://blog.crossjoin.co.uk/2013/02/01/office-2013-office-365-editions-and-bi-features/
https://www.powerpivotpro.com/2015/10/what-versions-of-office-2016-contain-power-pivot/