modifiable percentage field

[rehman1]

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

[Cmcmahan]

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.

[Cmcmahan · Accepted solution]

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?

[rehman1]

i think we are on the right track. somehow when i try to use this formula,CurA = MAX(ATable[ValA]) ) , i get a error saying "The expression specified in the query is not valid table expression"

i really appreciate your help 

[Cmcmahan]

That expression should be used for creating a measure, not a table.  The table should be generated with the GENERATESERIES(0,100) expression.

[rehman1]

Thank You so much for your support. I worked just as i wanted 

[rehman1]

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.

[rehman1]

---"So wait, is column C the one column that changes", (no, it remains constant)

 ----"column A is always 20% of column C" (No, the values in column may will change and is dependent on the input of user)

---"Also, do you want these to show up as percentages, or just a value that is some percentage of C?" (just a value that is some percent of column C)

--- "Who exactly is modifying this? " (the user will be modifying this)

               The 4th example which you have illustrated shows exactly what i want. Forget about D for now column A and B will be modifable field and depends on what the user inputs. sorry about th error in last message. what i want to say was that if column A ,B, and C have values 30% , 20% and 100 respectively then Column D will be 60.

are we on the same page?