Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
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!
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
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |