I have a Power Pivot file that is used for financial forecasting.
The model includes slicers that enable the user to choose different scenarios for what-if analysis. For example, there is a Sales slicer with values of 0, -5%, 5% and 10%. When the user selects one of these slicer values, total sales are multiplied by (1 + [User's Selection]).
So if total sales are $100 and the user selects 5%, total sales now show as $100,000 x (1+.05) = $105.
I accomplish this with a measure in the data model named Forecast Sales = Total Sales x Forecast Factor.
Forecast Factor is itself a measure, based on a table which lists the percentage values above. Forecast Factor = MAX(Percentage).
What I want to do is make the slicer more dynamic so the user can select his own what-if value. For example, the user may want to use 7.5% as the forecast factor instead of one of the options listed above.
I know I have seen a technique where the user can enter a value, which is then made an option in the slicer, but I can't find it now. I am able to find a lot of info on how to use the user input to filter data, but I can't figure out how to apply this to my situation. Any help is appreciated.
Thank you,
Bruce
The model includes slicers that enable the user to choose different scenarios for what-if analysis. For example, there is a Sales slicer with values of 0, -5%, 5% and 10%. When the user selects one of these slicer values, total sales are multiplied by (1 + [User's Selection]).
So if total sales are $100 and the user selects 5%, total sales now show as $100,000 x (1+.05) = $105.
I accomplish this with a measure in the data model named Forecast Sales = Total Sales x Forecast Factor.
Forecast Factor is itself a measure, based on a table which lists the percentage values above. Forecast Factor = MAX(Percentage).
What I want to do is make the slicer more dynamic so the user can select his own what-if value. For example, the user may want to use 7.5% as the forecast factor instead of one of the options listed above.
I know I have seen a technique where the user can enter a value, which is then made an option in the slicer, but I can't find it now. I am able to find a lot of info on how to use the user input to filter data, but I can't figure out how to apply this to my situation. Any help is appreciated.
Thank you,
Bruce