can you add a measure to Powerpivot that references a dyanmic cell in the calculation

[RSteinke]

We are creating a powerpivot report where the user would like to change a variable in a measure that we create so that they can see the effect on margins by changing this variable or cell location. For example: =([Measure.sales] * $A$1) where location A1 can be changed and the pivot table will show the results

 

[JavierGuillen]

The easiest way to allow for user input is by using linked tables in your powerpivot model. The downside is that after changing the cell value, he must open the powerpivot window (which will refresh the value in the model) and then refresh the pivot table manually.

 

[pvsenthil]

The easiest way to allow for user input is by using linked tables in your powerpivot model. The downside is that after changing the cell value, he must open the powerpivot window (which will refresh the value in the model) and then refresh the pivot table manually.

Hi Javier,

can you please post a sample formula here how to use that in calcualated measure?

 

[Tianbas]

It really depends what kind of user you have and how complex the calculation is.

If you have a big pivot whith complex measures and your input changes all of that, the proposal above is the best solution. Disadvantage of that is your user needs to open the PowerPivot Window after he changed the input cell and refresh the Pivot after that. If you create a linked table with a column Sales_Factor your calculated measure is a simple multiplication of your sales measure and the Min, Max or average of your column

If your table is more simple and has a fix structure like ASP, volume, Sales, product cost & margin I would propose to transform your pivot into cubeformulas (options->OLAP Tools->convert to formulas). This converts every cell of your pivot into a formula that you can change afterwards. You can use the results of the PowerPivot calculation and add some additional input cells to modify the final results.

 

[pvsenthil]

Hi Tianbas,

Thanks for the suggestion, yes that is the right solution.i have already converted.

-Senthil