Forum Discussion
How to multiply a column based on user selection
I have the original dataset as below
| ID | DayID | Amount | Type |
| 1 | 1 | 12 | A |
| 1 | 1 | 40 | B |
| 1 | 3 | 10 | A |
| 1 | 3 | 20 | C |
| 2 | 3 | 30 | D |
| 2 | 4 | 40 | A |
| 2 | 3 | 50 | C |
| 3 | 4 | 60 | B |
| 3 | 4 | 70 | C |
I have created a slicer from the values of 'Type'.
I want to create another column in the table that times 'Amount' by 1 by default and 0 if the user selects it.
For example if the user selects 'A' from the slicer all the amount values in the new column that have type A should be multiplied by 0. the resulting table should look like
| ID | DayID | Amount | Type | New Coulmn |
| 1 | 1 | 12 | A | 0 |
| 1 | 1 | 40 | B | 40 |
| 1 | 3 | 10 | A | 0 |
| 1 | 3 | 20 | C | 20 |
| 2 | 3 | 30 | D | 30 |
| 2 | 4 | 40 | A | 0 |
| 2 | 3 | 50 | C | 50 |
| 3 | 4 | 60 | B | 60 |
| 3 | 4 | 70 | C | 70 |
Can anyone please help with how I can do this?
3 Replies
Hi Anonymous ,
You could create a calculated table.
Table 2 = DISTINCT('Table'[Type])Then create a measure instead of a calculated column. Because a calculated column is static, while a masure is dynamic.
Measure = IF(ISFILTERED('Table 2'[Type])&&MAX('Table'[Type]) in ALLSELECTED('Table 2'[Type]),0*SUM('Table'[Amount]),SUM('Table'[Amount])*1)When the slicer is not selected,
When 'A' is selected,
You can check more details from my attachment.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You cannot modify the calculated column value based on slicer selection as it is loaded once on the data load and not every time as user interacts with the filter.
You can always create a measure using the SELECTEDVALUE and use that in your visualisations based on your requirement.
New Column = var SlicerSelection = SELECTEDVALUE(Table[Type]) return if SlicerSelection
