cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Change bar colour depending on percentage change from trend line

Hello

What formula can I use in a custom column to give a sales value a number based on criteria below. I am checking percentage difference in sales for each period against budget for each period and wanting to to use a custom column to change the colour of the bar, just am not very good with DAX.

1) If sales for period is more than 10% under budget then the bar is red, example below is P02 (sales 15 budget 30 = 50% under budget = 1)

2) if sales for period is within 10% of budget then bar is amber, example below is P08 (sales 38 Budget 40 = 10% within budget) or P11 (sales 32 budget 30 = 10% within budget = 2)

3) If sales for period is more than 10% over budget then bar is green, example below is P06 (sales 41 budget 30 = 24% over budget = 3)

Carlo

1 ACCEPTED SOLUTION
Community Support

Hi  @Brassodin ,

I created some data:

Here are the steps you can follow：

1. Create measure.

``````Measure =
DIVIDE(
MAX('MainTable'[Sales]),MAX('MainTable'[Budget])) -1``````
``````color =
SWITCH(
TRUE(),
[Measure] >= -0.5 && [Measure]<-0.1,"#4CAF50",
[Measure] >= -0.1 && [Measure]<=0.1,"#FFC107",
[Measure] > 0.1,"#f44336")``````

2. Format – Data color – Default color – fx.

3. Enter Default color – Data colors.

Format by – Field value

Based on field – [measure]

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 REPLIES 2
Community Support

Hi  @Brassodin ,

I created some data:

Here are the steps you can follow：

1. Create measure.

``````Measure =
DIVIDE(
MAX('MainTable'[Sales]),MAX('MainTable'[Budget])) -1``````
``````color =
SWITCH(
TRUE(),
[Measure] >= -0.5 && [Measure]<-0.1,"#4CAF50",
[Measure] >= -0.1 && [Measure]<=0.1,"#FFC107",
[Measure] > 0.1,"#f44336")``````

2. Format – Data color – Default color – fx.

3. Enter Default color – Data colors.

Format by – Field value

Based on field – [measure]

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Super User

@Brassodin I discussed similar problem and you can check it in this video https://www.youtube.com/watch?v=l2MRTFReCX0

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors