I tried to have variance between 2 tables using the following formula and it is ok with conditional formatting for Variance Act20 Vs Bud21.
Act20 Vs Bud21 = SUM(ActBudSumm4[Bud2021])-SUM(ActBudSumm4[Act2020])
However when I do % variance using the formula below and conditional formatting require a reference “Based on Field”. I add “Act20 Vs Bud21” (please refer conditional formatting screen shot).
It is mathematically correct. but it is not correct for accounts. Please let me know of work around.(please refer to Variance and % Variance screen shot)
%Act20 Vs Bud21 = FORMAT( DIVIDE([Act20 Vs Bud21],SUM(ActBudSumm4[Act2020])),“0.0%”)
I have funny % for Corporate Recharge. Is there I can limit the percentage with if statement. let say if the %Act20 Vs Bud21 is less than -100%, I would like to see as -100%.
How can I do that in DAX for my highlighted formula above.