How to multiply a column based on user selection

[Anonymous]

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?

[v-stephen-msft]

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.

[Anonymous]

@v-stephen-msft Thanks for that it looks promissing.

I want to take this a step further and plot  a histogram with Measure on the x-axis and count of ID on the y-axis.

How would I go about this as the histgram isn't letting me input Measure as values?

[PC2790]

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