Forum Discussion
modifiable percentage field
Hello, is there a way in powerBI to create a field which is modifying variable? here is a scenario:
there are 4 columns: A,B,C, Discount. I want column A and B to work as a varying/modifiable percentage field which should allow user to add any percentage value, from 0-100%. Column C will be a total amount, let say 500. column "Discount" will give us the discounbted value for example a user enters these values:
A B C Discount
25% 50% 500% =(A*B*C)
so i am wondering if there is a way to create a varying/modfiable field in PowerBI?
let me know if i wasnt clear or need explanation on anythin.
Thank you in advance
Sure. First you start with the allowed user inputs. Create a table with a single column that goes from 0-100 (or whatever range you want the % values to be available) for both A & B.
ATable = GENERATESERIES(0,100)
This will be the current % for each. Then create a slicer for A and one for B. Use the dropdown in the top right to change the format of these slicers to "Less than or equal to". This will be your input for the user.
Then set up a table with A, B, C, and D. (A&B in the table will need to be measures in the form of CurA = MAX(ATable[ValA]) ) Now D you set up as a measure like so:
Discount = [CurA]/100 * [CurB]/100 * SELECTEDVALUE(DataTable[C])
Pop all that into a table (make sure you tell it not to summarize C), and you get this:
And the sliders edit the table shown. Is this what you're trying to accomplish?
8 Replies
- Cmcmahan
Resident Rockstar
There are ways, but they're workarounds. PowerBI doesn't particularly like letting users change the data.
You could do this via slicer. Essentially create column A and column B which has the values 0-100 in it, and populate C however you'd like.
Then set up your Discount as a new measure with code like:
Discount = CALCULATE(SELECTEDVALUE(StaticTable[A]) * SELECTEDVALUE(StaticTable[B]) * DataTable[C])
And then in your visualization, set up a slicer for A and a slicer for B like an input box or slider as you like.
It's not pretty, but I think it gets done what you want.
Thanks for your reply.
The problem is that the Column A and Column B should be the percentage of Column C and i dont know how to find a way to make these two columns as a modifiable percentage column based on the value of Column C. for example:
Column A Column B Column C Column D
30% (30%OF100) 20% (20% of 100) 100 =(30%*20% *100%*100=)500
so i guess the question would be: how to create a percentage column which is the %value of another column
- Cmcmahan
So wait, is column C the one column that changes, and column A is always 20% of column C, or does the value in column C change as well as the % amount in column A? Also, do you want these to show up as percentages, or just a value that is some percentage of C? Because that does change the math you need to do. Your example doesn't make sense, because 30% * 20% * 100% * 100 is 6, not 500.
Which of these seems like the correct representation, given that A is always 30% of C and B is 20% of C:
Column A Column B Column C Column D Type of data 30% 20% 100 6 A&B are %, C&D are flat values 60% 40% 200% 48 % All 4 are % values 90 60 300 1,620,000 All 4 are flat values 120% 80% 400 96% A&B are %, C is flat, D is the % of C that multiplying all 3 gets If you set up columns A & B as calculated columns, then they'll change any time column C changes. Code would look similar to this, depending on which style of calculation you're doing.
Column A = Table[Column C] * .3
All of this is ignoring the modifiable percentage field part of your first question. Who exactly is modifying this? The user of the dashboard? The report maker? If it's some sort of modifiable field for the report user, instead of a hardcoded .3 like above, replace that with the value from the generated table from my first answer.
- Cmcmahan
Sure. First you start with the allowed user inputs. Create a table with a single column that goes from 0-100 (or whatever range you want the % values to be available) for both A & B.
ATable = GENERATESERIES(0,100)
This will be the current % for each. Then create a slicer for A and one for B. Use the dropdown in the top right to change the format of these slicers to "Less than or equal to". This will be your input for the user.
Then set up a table with A, B, C, and D. (A&B in the table will need to be measures in the form of CurA = MAX(ATable[ValA]) ) Now D you set up as a measure like so:
Discount = [CurA]/100 * [CurB]/100 * SELECTEDVALUE(DataTable[C])
Pop all that into a table (make sure you tell it not to summarize C), and you get this:
And the sliders edit the table shown. Is this what you're trying to accomplish?