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
RandomUser06
Frequent Visitor

Actuals vs Budget Table

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......

 

 

OperationsActualsBudgetVariance 
Op 1 y-x
Op 2a-b-a

Op3 

Op4 

klk-l=0
Op5    
Total   
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

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

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.