Whaif option in PP

[maring28]

Hi,

I'm bulding a report with PowerPivot, and I need to allow the users to use a field as what if field.
i.e, the user can change the value of one cell and this would affect all results in the pivot table (as the value of this cell embedded in several KFs).
In regular excel, it is simple, as I can address this cell within relevant formulas and calculations.
In PP, there is no option (except refreshing this cell every time in the PP tool and then the pivottable itself).

Ideas anyone?
I'm helpless... and if I don't solve this I would have to go back to "regular excel"...

Thanks alot!
Marina

 

[GDRIII]

Sounds like you need to set up a disconnected slicer.

Create a basic table with the "Selection" values you want to use in your interactive expression.

Bring that into the PP window.

Create an aggregate measure for that column using MAX, MIN, SUM.... So you would end up with something like [Selected Item]:=MAX(tblSelected(SelectedItem)

Now use that measure in your interactive measure. Let's say you had [Item Total] , you could do something like [Total Items]:=[Item Total]*[Selected Item]

Now add that SelectedItem field as a slicer on your PP table.

When the user selects whatever number from your slicer list, the math would be based on whatever you selected.

Search Disconnected Slicer for more...

 

[Tianbas]

It really depends on what you want to change/enter as input to your Pivot

If its only a simple selection like show me top 10 20 50 or use forecast exchange rate of 1,30 1,31 1,32 ,... you can do that with disconnected slicer as described above.

If you need to add some math to adjust the results after PowerPivot did the job like increase forecast revenue by X% you could use CUBE Formulas instead of a pivot to recieve the PowerPivot results and than calculate the adjustments from there in "normal" Excel

I have also seen a solution to add input parameter to PowerPivot File in a Sharepoint Report that. Not sure if this really changed the calculation in the model or only afterwards like the CUBE formula solution above

 

[maring28]

Thanks for your answers,
I actually need to use the input value to make further calculations with that value, and these calculations finally show in a pivot table in "normal" excel....
do you have any information how to use CUBE formulas??

 

[Tianbas]

To get started create a pivot in PowerPivot and go to Options->OLAP Tools->convert to formulas. This will translate every Filter and every value from your pivot in formulas in Excel cells
These cells you can adjust or use for further calcaulations. You can also move them around and format them as you want.

Main formulas you need are CUBEVALUE to pull a single value from PowerPivot and CUBEMEMBER that defines the Filter, row and column header to apply for CUBEVALUE

here you can find something more about it
Cube Formulas « PowerPivotPro