Forum Discussion
User modifiable % field
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
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.01Here'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///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.
4 Replies
kpost - It worked the way I required. Thank You.
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.01Here'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///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
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.