Data Tables (What-if-analysis) with Powerpivot

jemtan

New Member
Joined
Mar 22, 2016
Messages
1
Hi,

I have an Excel model that uses historical data as one of its inputs. The historical data is voluminous, hence instead of importing into excel and using VLOOKUP (or something similar), i opted to putting the huge database into MS Access, and then import into Powerpivot and using RELATED to reference the correct row. The model has several variables as its inputs. By changing these inputs, the model will then reference the correct historical data from the database that resides in Powerpivot's Data Model, extracts it back to Excel, and uses it as another input variable to then compute the final output.

Whenever I change one of the variables, I have to refresh the Data Model in Powerpivot, before the model calculates the correct output. To better visualize the range of possible outputs based on a range of combinations of inputs, I'm trying to use 2-way Data Tables (Excel's What-if-analysis function, not Powerpivot's), but it is not working as Excel's Data Table function does not refresh Powerpivot as it runs.

I had to resort to manually change the variables one at a time, each time refreshing Powerpivot, then copying and pasting the output of each combination of inputs into tables that are typically 10x10 or more. Obviously, this is extremely tedious and time consuming, especially when I have to tweak the model, and repeat it all.

I've tried googling this on other forums, and the closest solution is to use slicers where I can change the variables, and then see the output reflected without having to refresh Powerpivot (i think). *** far as I know, this still doesn't work with Data Tables.

Is there someway I can make Excel's what-if Data Tables work with Powerpivot?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,172,030
Messages
5,878,812
Members
433,372
Latest member
QuestioningControllerBE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top