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

[vanonyuo]

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?

[FrankAT · Accepted solution]

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

[FrankAT · Accepted solution]

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 · Accepted solution]

@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!