cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jtan20
Frequent Visitor

How to multiply a column based on user selection

I have the original dataset as below

 

IDDayIDAmountType
1112A
1140B
1310A
1320C
2330D
2440A
2350C
3460B
3470C

 

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

 

IDDayIDAmountTypeNew Coulmn
1112A0
1140B40
1310A0
1320C20
2330D30
2440A0
2350C50
3460B60
3470C70

 

Can anyone please help with how I can do this?

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

Hi @jtan20 ,

 

You could create a calculated table.

Table 2 = DISTINCT('Table'[Type])

vstephenmsft_0-1650360963955.png

 

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,

vstephenmsft_1-1650361072972.png

When 'A' is selected,

vstephenmsft_2-1650361103049.png

 

 

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.

 

@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
Super User
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

 

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors