Multiply each column by 100 for a given condition-PowerBI

[deb_power123]

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.

Please suggest an approach.

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.

Appreciate for all your help.

Regards

Sameer

[v-yiruan-msft]

Hi @deb_power123 ,

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

[PhilipTreacy · Accepted solution]

Hi @deb_power123 

Download sample PBIX with this measure

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

[deb_power123]

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