cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Conditional Formatting for Bar Charts

Hi All. I am currently banging my head trying to get what I thought was a simple conditional formatting (RAG indicator) to appear on a single bar chart. Thanks in advance if someone can point me in the right direction and if I find a solution beforehand I shall post here for the community.

 

Scenario and Data

I have multiple rows that represent "projects" which have columns for [Targets] both annual and monthly and also a column for [Enabled].

I am aiming to produce a bar chart that shows the total [Enabled] with the [Target] amount shown as a dashed line. This bit I have managed quite easily with a standard KPI style bullet bar chart.

The next step is to add conditional formatting based on the rule below that changes the colour of the bar depending on it's relationship with the [Target column]

 

Rule

2019/20 Savings RAG = if([Enabled 2019/20]>=[Savings Targets 2019/20],"Blue",if([Enabled 2019/20]>=[2019/20 Current Month Target],"Green",if(month(edate(now(),-3))<7,if([Enabled 2019/20]>=([2019/20 Current Month Target]*0.7),"Amber","Red"),"Red")))

In summary, various conditions and thresholds trigger different colours (Blue, Green, Amber, Red).

 

Attempts

I have attempted to split the colours up to different columns and if true return 1 and if false return 0. For example:

2019/20 RAG Blue = if([Enabled 2019/20]>=[Savings Targets 2019/20],1,0)

 

Best Regards,

Mark

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: Conditional Formatting for Bar Charts

Hi @Mark15 

You could create a measure like below, essentially its your rules with colours replaced with numbers. 

coloursX = 
SWITCH(
    TRUE,
    [Enabled 2019/20] >= [Savings Targets 2019/20], 1, --Blue 
    [Enabled 2019/20] >= [2019/20 Current Month Target], 2, -- Green
    MONTH( EDATE( NOW(), -3 ) ) < 7 && [Enabled 2019/20] >= ( [2019/20 Current Month Target] * 0.7 ), 3, --Amber
    0 --Red
)

Next, in the Visualization Pane > Format > Data Colors, Click on the 3 dots and Select Conditional Formating to open the options ( as on the sreenshot below ) 
image.png

Once in the Conditional Formatting settings, just make sure you set everything  as below

 

image.png

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

View solution in original post

3 REPLIES 3
Highlighted
Super User V
Super User V

Re: Conditional Formatting for Bar Charts

Hi @Mark15 

You could create a measure like below, essentially its your rules with colours replaced with numbers. 

coloursX = 
SWITCH(
    TRUE,
    [Enabled 2019/20] >= [Savings Targets 2019/20], 1, --Blue 
    [Enabled 2019/20] >= [2019/20 Current Month Target], 2, -- Green
    MONTH( EDATE( NOW(), -3 ) ) < 7 && [Enabled 2019/20] >= ( [2019/20 Current Month Target] * 0.7 ), 3, --Amber
    0 --Red
)

Next, in the Visualization Pane > Format > Data Colors, Click on the 3 dots and Select Conditional Formating to open the options ( as on the sreenshot below ) 
image.png

Once in the Conditional Formatting settings, just make sure you set everything  as below

 

image.png

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

View solution in original post

Highlighted
New Member

Re: Conditional Formatting for Bar Charts

Much appreciated this works a charm. I added sum() to the columns within the measure and this cleared any errors I subsequently had.

 

Thanks again. Mark

Highlighted
Super User V
Super User V

Re: Conditional Formatting for Bar Charts

Hi @Mark15 

No problem, happy to help!

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors