Actuals vs Budget Table

[RandomUser06]

Hey All, 

I need some assistance with a report i am building. The report is an actuals vs budget report with a variance column. The report looks at different operations we have around the world and measures their technology spend. The goal is for the Actuals to be lower than the Budget and the variance helps us look at how much an operation is overspending or underspending. 

What i need some assistnace with is a couple of things. I have a table and i need the values of the variance to indiacte red anytime there is a negative value greater than 5% of the variance value, Indicate amber if the value is equal to + - 5% of the variance and green if the value is positive and greater than 5%.

I am also running into a creative block and would greatly appreciate it if somone could give me ideas as to how else i can present this report so its easier for users to understand how much each ops is spending. 

Greatly appreciate the help! 🙂 

The report i have is set out like this 

Slider to select year                                                               Slider to select ops - op 1 op 2 op 3 op 4......

Operations	Actuals	Budget	Variance
Op 1	x	y	y-x
Op 2	a	b	-b-a
Op3  Op4	k	l	k-l=0
Op5
Total

[amitchandak · Accepted solution]

@RandomUser06 , You need to have measure likes, assuming Actual and Budget are measures

Sumx(values(Table[Operaions]) ,

Switch(true(),

Max(Table[Operations]) = "Op1" , [Budget] - [Actual] ,

Max(Table[Operations]) = "Op2" , -[Budget] - [Actual] ,

// Add other rules.

)

The same way you can create a color measure too  for conditional formatting and use that in cell element color using the field value option

How to do conditional formatting by measure and applying it to pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/...
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...