Forum Discussion

vanonyuo's avatar
vanonyuo
Frequent Visitor
6 years ago
Solved

How can I convert this Excel Formula to a Column calculation in Power BI?

Lets say Annual pay is 70000, Final A is 10000. Discretionary is 1000.

Final FA should be = 841.51

 

Excel: Final FA =IF((((@AnnualPay/12)*2)+@Final_A_Amount+@Discretionary_Award)<137700,(@Final_AIP_Amount+@Discretionary_Award)*0.0765,(@Final_AIP_Amount+@Discretionary_Award)*0.0145)

 

But below is what I entered in Power BI, and the Final FA is duplicating final_A_Amount = 10000

 

Final FA = IF(('table1'[ANNUALPAY]/12)*2 + 'Table1'[Final_A_ Amount] + RELATED('Table2'[Discretionary Award]) <137700, 'Table1'[Final_A_Amount]+RELATED('Table2'[Discretionary Award])*0.0765,

'Table1'[Final_A_Amount] + RELATED('Table2'[Discretionary Award])*0.0145)

 

How can I get the accurate result like the excel formula?

Need Help
  • FrankAT's avatar
    FrankAT
    6 years ago

    Hi vanonyuo 

    use the following formula:

     

    Final FA =
IF(
	(('Table1'[AnnualPay]/12) * 2 + 'Table1'[Final_A_Amount] + 'Table1'[Discretionary_Award]) < 137700,
	('Table1'[Final_AIP_Amount] + 'Table1'[Discretionary_Award]) * 0.0765,
	('Table1'[Final_AIP_Amount] + 'Table1'[Discretionary_Award]) * 0.0145
)

     

    Regards FrankAT

  • vanonyuo's avatar
    vanonyuo
    6 years ago

    FrankAT 

     

    Thank you for you swift response. Discretionary is in a different table 2 and I can only use RELATED to add. So I

    So I amended a bit and It worked!!!

     

    Final FA =
    IF(
    ('Table1'[AnnualPay]/12) * 2 + 'Table1'[Final_A_Amount] + RELATED('Table1'[Discretionary_Award]) < 137700,
    ('Table1'[Final_AIP_Amount] + RELATED('Table1'[Discretionary_Award])) * 0.0765,
    ('Table1'[Final_AIP_Amount] + RELATED('Table1'[Discretionary_Award])) * 0.0145
    )

     

    Thank you So Much!

2 Replies

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi vanonyuo 

    use the following formula:

     

    Final FA =
IF(
	(('Table1'[AnnualPay]/12) * 2 + 'Table1'[Final_A_Amount] + 'Table1'[Discretionary_Award]) < 137700,
	('Table1'[Final_AIP_Amount] + 'Table1'[Discretionary_Award]) * 0.0765,
	('Table1'[Final_AIP_Amount] + 'Table1'[Discretionary_Award]) * 0.0145
)

     

    Regards FrankAT

    • vanonyuo's avatar
      vanonyuo
      Frequent Visitor
      6 years ago

      FrankAT 

       

      Thank you for you swift response. Discretionary is in a different table 2 and I can only use RELATED to add. So I

      So I amended a bit and It worked!!!

       

      Final FA =
      IF(
      ('Table1'[AnnualPay]/12) * 2 + 'Table1'[Final_A_Amount] + RELATED('Table1'[Discretionary_Award]) < 137700,
      ('Table1'[Final_AIP_Amount] + RELATED('Table1'[Discretionary_Award])) * 0.0765,
      ('Table1'[Final_AIP_Amount] + RELATED('Table1'[Discretionary_Award])) * 0.0145
      )

       

      Thank you So Much!