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%”)
and also
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.
@ammu Would you really want your outflows to be stored as negative numbers? I get that’s how they’re treated in the P&L but a positive variance could be favorable or unfavorable based entirely on which line item it is. That might be how I’d design the conditional formatting.
Hi Tim,
That is exactly what I want. Is there any video how to treat numbers to get Favourable and Unfavourable variance a well as for conditional formatting.
can you please refer me to the specific video clip.
Many thanks.
Both of them are offering the possibility to conditionally color the variance as “Good” or “Bad” … plus they give you the option of using Small Multiples.
Hi @ammu, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!
