cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 3
Community Support

Hi @jtan20 ,

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.

Frequent Visitor

@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?

Super User

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``````

Announcements