cancel
Showing results for
Did you mean:
Helper III

## Multiply each column by 100 for a given condition-PowerBI

HI All,

I have a scenario where I need to multiply each of the fees column in School table by 100 for year >=2010 as a calculated column or measure.

I used the below measure to set the flag to '1' for all those School[Year] >=2010 but now I need to multiply the Fees column with 100 for all those flag values which are set to '1' in Flag column and put the values in a new column say 'FeesFinal'

Flag = If(School[Year]>=2010,1,0)

As an approach I created a MFactor table with year and Factor [which has 100 as value] .I related this table by Year column to School table and tried to create a measure to multiply the fees column of School table with Factor column of MFactor table.ButI got below error.

Input Data :

 StudentID Year Fees 100 2010 2000 101 2011 2100 102 2009 1800 103 2008 1100 104 2005 900 105 2012 1800

Expected Output:

 StudentID Year Fees Flag FeesFinal 100 2010 2000 1 200000 101 2011 2100 1 210000 102 2009 1800 0 NA 103 2008 1100 0 NA 104 2005 900 0 NA 105 2012 1800 1 180000

Please suggest any  approach to achieve this.

Regards

Sameer

1 ACCEPTED SOLUTION
Super User III

You can use a measure and you don't need to create a Flag column

``FeesFinal = IF(SELECTEDVALUE('Table'[Year]) >= 2010, CALCULATE (SELECTEDVALUE('Table'[Fees]) * 100, FILTER('Table', 'Table'[Year] >= 2010)), SELECTEDVALUE('Table'[Fees]))``

Regards

Phil

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

3 REPLIES 3
Community Support

You can create a calculated column or measure to achieve it:

1. Calculated column

``FeesFinal = IF ( 'Table'[Year] >= 2010, 'Table'[Fees] * 100, 'Table'[Fees] )``

2. Measure

``````Measure for FeesFinal =
VAR _curyear =
SELECTEDVALUE ( 'Table'[Year] )
VAR _curfees =
SELECTEDVALUE ( 'Table'[Fees] )
RETURN
IF ( _curyear >= 2010, _curfees * 100, _curfees )``````

You can review the following blogs to decide which one(create a calculated column or a measure) is better in your scenario.

Calculated Columns vs Measures

Calculated Columns and Measures in DAX

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User III

You can use a measure and you don't need to create a Flag column

``FeesFinal = IF(SELECTEDVALUE('Table'[Year]) >= 2010, CALCULATE (SELECTEDVALUE('Table'[Fees]) * 100, FILTER('Table', 'Table'[Year] >= 2010)), SELECTEDVALUE('Table'[Fees]))``

Regards

Phil

If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!

Helper III

Thankyou for the awesome approach, yes this worked for my case.Sorry to reply late ...

Announcements