cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
deb_power123
Helper III
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.

 

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)
 

1.JPG

 

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.

2.JPG

 

4.JPG

 

Input Data :

StudentIDYearFees
10020102000
10120112100
10220091800
10320081100
1042005900
10520121800

 

Expected Output:

StudentIDYearFeesFlagFeesFinal
100201020001200000
101201121001210000
102200918000NA
103200811000NA
10420059000NA
105201218001180000

 

Please suggest any  approach to achieve this.

Appreciate for all your help.

 

Regards

Sameer

1 ACCEPTED SOLUTION
PhilipTreacy
Super User III
Super User III

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]))

 

 

fees.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

3 REPLIES 3
yingyinr
Community Support
Community Support

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 )

yingyinr_0-1614577312325.png

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.
PhilipTreacy
Super User III
Super User III

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]))

 

 

fees.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.