Forum Discussion
Using a parameter in desktop
Hello everyone,
Hope someone can assist with my parameter issue which is causing a lot of headache.
I have two tables; (A) with three ID's and three products for each, and (B) which a discount at a given qty supplied:
| TABLE A | ||
| ID | Product | Cost1 |
| A | X | 48 |
| A | Y | 50 |
| A | Z | 24 |
| B | X | 25 |
| B | Y | 50 |
| B | Z | 45 |
| C | X | 31 |
| C | Y | 46 |
| C | Z | 35 |
| TABLE B | ||
| ID | Supplied qty | Discount |
| A | 10 | -3 |
| A | 20 | -8 |
| A | 30 | -20 |
| A | 40 | -8 |
| A | 50 | -15 |
| A | 60 | -20 |
| A | 70 | 0 |
| B | 10 | -14 |
| B | 20 | -17 |
| B | 30 | -16 |
| B | 40 | -1 |
| B | 50 | -7 |
| B | 60 | -16 |
| B | 70 | -4 |
| C | 10 | -6 |
| C | 20 | -5 |
| C | 30 | -17 |
| C | 40 | -14 |
| C | 50 | -7 |
| C | 60 | -3 |
| C | 70 | -18 |
What I would like to do is to enable the report reader to adjust the supplied qty by using a parameter and then have a calculated table showing the new cost (2) i.e. if supplied qty is set to 10:
| Cost2 | |||
| ID \ Product | X | Y | Z |
| A | 45 | 47 | 21 |
| B | 11 | 36 | 31 |
| C | 25 | 40 | 29 |
and if set to 50:
| Cost2 | |||
| ID \ Product | X | Y | Z |
| A | 33 | 35 | 9 |
| B | 18 | 43 | 38 |
| C | 24 | 39 | 28 |
I have created a parameter in Power BI (GENERATESERIES(10; 70; 10)) which can be used to show the discount for each ID according to the given qty in the parameter in a table. However, I can't figure out how to calculate the new cost as shown above.
Any ideas please?
Thanks
Top006 , You need have common table ID
measure =
var _value = selectedvalue(series[value])
return
sum(TableA [Cost]), calculate(Sum(TableB[Discount]), filter(TableB, TableB[ID] = Max(ID[ID]) && TableB[Supplied qty] =_value))Bridge Table https://youtu.be/Bkf35Roman8
2 Replies
Top006 , You need have common table ID
measure =
var _value = selectedvalue(series[value])
return
sum(TableA [Cost]), calculate(Sum(TableB[Discount]), filter(TableB, TableB[ID] = Max(ID[ID]) && TableB[Supplied qty] =_value))Bridge Table https://youtu.be/Bkf35Roman8
