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?
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?
