Running pivot table scenarios without duplicating the whole table

[urazack]

Hello, This is my first post on Excel Forum.

I've learnt how powerful tables and pivot tables are for generating views/reports of tabular data. As a result, I've taken production supply data at my company and put it into a table and have created views/reports that others can just refresh each time data is added to the bottom of the table.

The one challenge I now have is that sometimes my team is asked for a report IF a specific production line has slightly different numbers. Currently, to do this, I would duplicate the entire table below itself and, in a new "Scenarios" column, add text like "Scenario 2" in each row. (I'd add "Scenario 1" next to each row in the "Scenarios" column of the original table.) I'd then have to simply add the "Scenarios" column to the "Filter" box in the pivot table options and use this to choose report on the specific scenario.

The challenge I have with this approach is that the original table can be quite large e.g. 10,000 rows and the changes may only affect 50-100 rows. We may also be asked to run several such scenarios. Hence it seems inefficient to have to duplicate 10,000 rows multiple times for each scenario.

Is there a more effective approach where one can keep the existing table and perhaps only add rows for the changes, and have the changes be pulled into the respective scenarios in the pivot table views/reports?

I've tried to illustrate the challenge in the sample spreadsheet at the following link:
drive.google.com/file/d/1jSu8Lg032etYgRkQ9X9hoLr_ZcGaZgqa/view

Thanks for the help!

Kind regards,
Uwais

[teylyn]

Hello,

have you considered simply adding columns for each scenario to the source data? For example,

"New Request: Segment B figures for 2020 and 2021 should be increased by 10%" - Add a column with the formula

=IF(AND([@Segment]="Segment B",OR([@Year]="2020",[@Year]="2021")),[@Value]*1.1,[@Value])

Then create a pivot table with the original value column, copy and paste the pivot table and select the scenario value column in the copy.

That way you don't have to duplicate all your data, but just the pivot table, which still uses the same pivot cache, so it's not a big strain on the resources.

2019-01-22_13-20-23.png

[urazack]

Thank you Teylyn! I think your suggestion will definitely work better in many scenarios. I will keep that in mind.

If I think ahead, the challenge that I may see arise is that next year, we'll have a new base long term forecast ("Scenario A") for all the data points. This base forecast may even include new production segments. During this year, we may then run further scenarios e.g. "Scenario B". In this example I see the new year's forecast being appended to the bottom of the table and then running scenarios in new columns as you suggested. As the years add up and scenarios multiply, I see there being quite a lot of duplicate data. See the orange highlighted cells in the picture at the link below, or the "Solution 2" tab of the spreadsheet at the link below.

Can you think of any alternative solution where you only need to add in the data that has changed and somehow pull in all the remaining data from balance of the table? With reference to the picture, is there a solution that doesn't require adding the data in the grey italicized cells ("Duplicate data").

Thank you.

drive.google.com/open?id=1kBiuT5v_Sdhy44oua-i25yZkdwpz1dg7
drive.google.com/open?id=1aGcZG2EtgSsV1hT-nMj-dyHQfrwABjyS (see "Solution 2" tab)

[teylyn]

Can you think of any alternative solution where you only need to add in the data that has changed and somehow pull in all the remaining data from balance of the table?

Not really. Pivot tables can only show existing data. If there are no rows in the source data for Scenario B, 2019, then there is no way to show that in the pivot table.

If you are concerned about the size of the file when duplicating the data, you could engage Power Pivot to make things easier.

Here's how this could work:

Use flat tables in individual workbooks for each scenario. The table structure should be like your source tables in Solution 1. If there is a new scenario, copy an existing workbook and change the data. Each workbook has the full data set for one scenario.

Then, start a new workbook and use Power Query to create queries to all the scenario workbooks, append the tables into one table. You can place all scenario workbooks into one folder and create a query that combines all the tables of all the workbooks in the folder. Whenever you need a new scenario, save it in the folder and Power Query will pick it up on the next data refresh. Power Query is very flexible.

That last query, with all the data from all the workbooks in that specific folder does not need to get loaded into a worksheet either. Create just the connection, but make sure to tick the box to load that query into the Data Model.

Now can create the pivot tables from the Data Model where all the scenarios are grouped, as in your solution 1.

The benefit of the Power Pivot approach is that the Data Model has a compression algorithm that stores a lot of data (millions of rows) immensely efficiently. You may find that the workbook with the Data Model is actually a lot smaller than the source workbooks.

I assume that as a business, Power Pivot is included in your 365 license of Excel. Give it a try.