• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

User Inputs slicer Value

B

BKaufmann

New Member
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
 
How would you have user enter value? Is it going to be some defined range in worksheet?

You may consider leveraging PowerQuery and using List.Generate to create dynamic table.

If you can upload small sample of your set up (with sanitized data). I can take a look.
 
Here's how to set up dynamic table.
Which creates list of values from Max until nearest value greater than Min, decremented by step value.

First set up 3 named ranges (each containing single cell only).
lstMax
lstMin
lstStep

Then in Query Editor, add blank query and copy below.
Code: 
let
    vMax = Excel.CurrentWorkbook(){[Name="lstMax"]}[Content]{0}[Column1],
    vMin = Excel.CurrentWorkbook(){[Name="lstMin"]}[Content]{0}[Column1],
    vStep = Excel.CurrentWorkbook(){[Name="lstStep"]}[Content]{0}[Column1],
    Source = Table.FromList(List.Generate(()=>vMax, each _ > vMin, each _ - vStep), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Percentage"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Percentage", type number}}),
    #"Rounded Off" = Table.TransformColumns(#"Changed Type",{{"Percentage", each Number.Round(_, 3), type number}})
in
    #"Rounded Off"

Using Max = 0.25, Min = -0.06, Step = 0.05 following table is generated.
upload_2017-4-12_7-55-44.png

You can load this to data model. When values contained in named range changes, table will update itself when refreshed.

Note: I did not code error handling. You can do this either at user input level (via data validation etc) or in PowerQuery by adding if statement checks for each of variables.
 
Chihiro said:
Here's how to set up dynamic table.
Which creates list of values from Max until nearest value greater than Min, decremented by step value.

First set up 3 named ranges (each containing single cell only).
lstMax
lstMin
lstStep

Then in Query Editor, add blank query and copy below.
Code: 
let
    vMax = Excel.CurrentWorkbook(){[Name="lstMax"]}[Content]{0}[Column1],
    vMin = Excel.CurrentWorkbook(){[Name="lstMin"]}[Content]{0}[Column1],
    vStep = Excel.CurrentWorkbook(){[Name="lstStep"]}[Content]{0}[Column1],
    Source = Table.FromList(List.Generate(()=>vMax, each _ > vMin, each _ - vStep), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Percentage"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Percentage", type number}}),
    #"Rounded Off" = Table.TransformColumns(#"Changed Type",{{"Percentage", each Number.Round(_, 3), type number}})
in
    #"Rounded Off"

Using Max = 0.25, Min = -0.06, Step = 0.05 following table is generated.
View attachment 40669

You can load this to data model. When values contained in named range changes, table will update itself when refreshed.

Note: I did not code error handling. You can do this either at user input level (via data validation etc) or in PowerQuery by adding if statement checks for each of variables.
Click to expand...
Chihiro said:
Here's how to set up dynamic table.
Which creates list of values from Max until nearest value greater than Min, decremented by step value.

First set up 3 named ranges (each containing single cell only).
lstMax
lstMin
lstStep

Then in Query Editor, add blank query and copy below.
Code: 
let
    vMax = Excel.CurrentWorkbook(){[Name="lstMax"]}[Content]{0}[Column1],
    vMin = Excel.CurrentWorkbook(){[Name="lstMin"]}[Content]{0}[Column1],
    vStep = Excel.CurrentWorkbook(){[Name="lstStep"]}[Content]{0}[Column1],
    Source = Table.FromList(List.Generate(()=>vMax, each _ > vMin, each _ - vStep), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Percentage"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Percentage", type number}}),
    #"Rounded Off" = Table.TransformColumns(#"Changed Type",{{"Percentage", each Number.Round(_, 3), type number}})
in
    #"Rounded Off"

Using Max = 0.25, Min = -0.06, Step = 0.05 following table is generated.
View attachment 40669

You can load this to data model. When values contained in named range changes, table will update itself when refreshed.

Note: I did not code error handling. You can do this either at user input level (via data validation etc) or in PowerQuery by adding if statement checks for each of variables.
Click to expand...


Chihiro said:
How would you have user enter value? Is it going to be some defined range in worksheet?

You may consider leveraging PowerQuery and using List.Generate to create dynamic table.

If you can upload small sample of your set up (with sanitized data). I can take a look.
Click to expand...

Thank you very much for this information! It is exactly what I was looking for. I assumed it would require a named range and I hoped coding would not be required, but whatever works, right?

Let me be sure that I understand what you have suggested. Instead of a table, which is what I have now in the data model, I am putting a query in the data model. The details of the query are contained in the code you provided.

When I enter the query into the data model, then whenever the user changes the value in the specified cells, the values will change on the worksheet.

I have some questions:

1) Does the user have to hit refresh after entering the data in the named ranges or does the update happen automatically as soon as the new data is entered in the named range?

2) Using this technique, I am assuming I don't need to have a slicer since the named range input cell serves the same purpose.

3) What language is that code?

I appreciate your offer to look at a sanitized file, but to help me learn the concept I will first try to implement what you have suggested. If I fail to implement it correctly, I'll send a file so you can help me determine where I made my error.

Again, thank you for providing an answer I knew existed but couldn't find.

Bruce
 
1) You need refresh. You can use VBA to force refresh on change if needed.

2) This one is really designed to work with slicer. Since it will contain Max to >Min value and decremented value in between. If you want specific value (which you use in calculation) you'll need to change things around quite a bit.

3) It's PowerQuery programming language known as "M".
 
Chihiro said:
1) You need refresh. You can use VBA to force refresh on change if needed.

2) This one is really designed to work with slicer. Since it will contain Max to >Min value and decremented value in between. If you want specific value (which you use in calculation) you'll need to change things around quite a bit.

3) It's PowerQuery programming language known as "M".
Click to expand...

Thank you. Looking at it again, more seems clear. Please confirm if this is correct.

1. I'm not adding a query to the data model. Instead, I am using a query (your code) to populate a table in the data model.

2. Your code enables the user to specify the range of values - minimum, maximum and step value - instead of just entering one value.

3. Refresh is needed in order to force the table to run the query again so the values in the table are the values specified by the user.

4. The user will see both a slicer and the input cells on the worksheet. When the user enters the min, max and step values, then hits refresh, the values in the slicer will change, and then the user can select the desired slicer value to apply to the data in the worksheet.

Another question: If I want the user to be able to only input one value, would I modify the code to have only, say, vPct, and only one named range on the worksheet for data input?
 
1. You can go either direction. As long as you get the end result you want.

2. Correct

3. Correct

4. Correct

I've never just passed single value from PowerQuery to PowerPivot. But it should be possible. And yes, you need only 1 named range.

Edit: You can't use List.Generate without Min & Max parameter. You'll need to significantly modify the code.
 
Chihiro said:
1. You can go either direction. As long as you get the end result you want.

2. Correct

3. Correct

4. Correct

I've never just passed single value from PowerQuery to PowerPivot. But it should be possible. And yes, you need only 1 named range.

Edit: You can't use List.Generate without Min & Max parameter. You'll need to significantly modify the code.
Click to expand...

Thanks. I have to clean up some other things, then I can try your approach. I'll let you know how it worked out.
 
Here's sample using single value to create table.
Code: 
let
    vPct = Excel.CurrentWorkbook(){[Name="lstParam"]}[Content]{0}[Column1],
    Source = Table.FromList({vPct}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Percentage"}}),
    #"Rounded Off" = Table.TransformColumns(#"Renamed Columns",{{"Percentage", each Number.Round(_, 3), type number}})
in
    #"Rounded Off"
 
You must log in or register to reply here.
Back
Top