Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vanonyuo
Frequent Visitor

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?

2 ACCEPTED SOLUTIONS
FrankAT
Community Champion
Community Champion

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

View solution in original post

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

View solution in original post

2 REPLIES 2
FrankAT
Community Champion
Community Champion

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 

 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.