User modifiable % field

[toovishant]

Hi Team,

This may be tricky, I am trying to have a modifiable % (0-100) field which allows the user to change the % in these rows.

Please find the sample view in excel that I require the same in Power BI

In the below sample view

In Store there are "N" no of products quantity. Below would give user on a given week day to change the % if sold on that day would  sell the "X" quantity(Q) of product (P)

PQ = 900 (which change depending upon the choosen area)

Week Day        Total Product        editable (%) 

Monday                 PQ*10%               10%

Tuesday                 PQ*15%                15%

Wednesday           PQ*10%                 10%

Thursday               PQ*10%                 20%

Friday                    PQ*10%                 30%

Please help

Thanks,

Vishant

[kpost · Accepted solution]

I'm a little unclear on the problem, but I can provide you with a solution when you need to have user-inputted numbers used in a DAX query, it's called a "Numeric Range Parameter".  Perhaps you can use this walk-through and attached .pbix file to fit to your exact situation.  I think what you will end up having to do is create FIVE different parameters, one for each day.

It's under Modeling -> New Parameter -> Numeric Range

 Fill it out like so.  I've chosen to have the increments be 1 (0, 1, 2, 3, 4   .... 99, 100) but you could choose 5 if you wanted, (0, 5, 10, 15  ... 95, 100)

 

This will add the parameter as a slicer on your page, which you can reference in DAX queries.

Here's an example where I have a measure equal to X % of 500.  Note multiplying it by 0.01 because the user is selecting a value between 0 and 100, not 0.00 and 1.00.

500_times_selected_value = 500 * [User Selected Percent Value] * 0.01

Here's the result with 50% selected  (500 * 50 * 0.01 = 250)

Here's the result with 20% selected  (500 * 20 * 0.01 = 100)

Then I would combine this with a switch statement that incoporates your [week day] column, raw [Product Quantity] values, and your user-defined parameters.

Something like this:

total Product = 
VAR day = SELECTEDVALUE([Week Day])
return
    SWITCH(
     TRUE(),
    day = "Monday", [Product Quantity] * [Monday Selected Percent],
    day = "Tuesday", [Product Quantity] * [Tuesday Selected Percent],
    day = "Wednesday", [Product Quantity] * [Wednesday Selected Percent],

etc........

)

See attached .pbix file.  Hopefully this is close to what you are looking for, or you can otherwise figure out how to use a parameter value to solve your problem.

///Mediocre Power BI advice, but it's free///

[toovishant]

@kpost  - It worked the way I required. Thank You.

[kpost · Accepted solution]

I'm a little unclear on the problem, but I can provide you with a solution when you need to have user-inputted numbers used in a DAX query, it's called a "Numeric Range Parameter".  Perhaps you can use this walk-through and attached .pbix file to fit to your exact situation.  I think what you will end up having to do is create FIVE different parameters, one for each day.

It's under Modeling -> New Parameter -> Numeric Range

 Fill it out like so.  I've chosen to have the increments be 1 (0, 1, 2, 3, 4   .... 99, 100) but you could choose 5 if you wanted, (0, 5, 10, 15  ... 95, 100)

 

This will add the parameter as a slicer on your page, which you can reference in DAX queries.

Here's an example where I have a measure equal to X % of 500.  Note multiplying it by 0.01 because the user is selecting a value between 0 and 100, not 0.00 and 1.00.

500_times_selected_value = 500 * [User Selected Percent Value] * 0.01

Here's the result with 50% selected  (500 * 50 * 0.01 = 250)

Here's the result with 20% selected  (500 * 20 * 0.01 = 100)

Then I would combine this with a switch statement that incoporates your [week day] column, raw [Product Quantity] values, and your user-defined parameters.

Something like this:

total Product = 
VAR day = SELECTEDVALUE([Week Day])
return
    SWITCH(
     TRUE(),
    day = "Monday", [Product Quantity] * [Monday Selected Percent],
    day = "Tuesday", [Product Quantity] * [Tuesday Selected Percent],
    day = "Wednesday", [Product Quantity] * [Wednesday Selected Percent],

etc........

)

See attached .pbix file.  Hopefully this is close to what you are looking for, or you can otherwise figure out how to use a parameter value to solve your problem.

///Mediocre Power BI advice, but it's free///

[toovishant]

Hi @kpost,

Sorry for the limited sample data. Apperciate your quick response.

I require the "Editable %" column user can input the % for each Weekday The above slicer % applies the same accross the weekday, is there a way to customize the % input for each week day?

Thank You.

Vishant

[kpost · Accepted solution]

You will need to create a parameter for each week day ([Monday Selected Percent], [Tuesday Selected Percent], [Wednesday Selected Percent] etc.)  then use a SWITCH statement like this

total Product = 
VAR day = SELECTEDVALUE([Week Day])
return
    SWITCH(
     TRUE(),
    day = "Monday", [Product Quantity] * [Monday Selected Percent],
    day = "Tuesday", [Product Quantity] * [Tuesday Selected Percent],
    day = "Wednesday", [Product Quantity] * [Wednesday Selected Percent],

etc........

)

It is not possible to have a user edit values in a table visual.