Combine pivot tables with different but compatible measures?

Question

I have a pivot table that displays agencies in rows, products in columns and sales units as values. I have to make up a report in this format:

Data is coming from an SQL Server Analysis Services and the "Estimate 2015" measure is a different measure (but uses the same dimensions and granularity so it is in fact possible to display the values side by side).

I could add a separate pivot table for the estimates, but then filtering or sorting the two tables will make them loose sync.

Is it possible to somehow align or combine pivot tables with different measures nicely?

Answer 1

if you are using Excel 2013, then you can use the data-model feature with standard pivot tables.

You need on your workbook:

• 3 dimension tables for agencies, products and year. All rows must contain distinct values (Agency A-C, Product A-B, Year 2013-2015)
• 2 fact tables: the one with historical data (from SSAS), the second one with your forecast datas

then you just create a Pivot table from any of the Dimension table. At this step, check the box "Add this data to the Data Model".

You then pull the Dimension field from the three dimension table on the Pivot column and rows. Choose both value fields from the 2 fact table (Quantity). Excel detect missing relationships. You have to build them manually between the two Facts tables and the three dimensions (total 6 relationships)

In this Excel-file you can see an example with your sample datas.

You can find on the web some step-by-step explanations like How to Build PivotTables Using Excel's Data Model Feature