Conditional Formatting a Bar Chart using a measure

[Clockwise]

I am trying to simply add this conditional color to bar chart such that if it is more than average one color be used and if it is less than average another color. Take Red and Green. 

Here is the measure I created

Bar_color_PastDue = IF(SELECTEDVALUE('Summary'[Total Past Due])-SELECTEDVALUE('Summary'[Average_TotalPastDue])>0, "red", "Green")

then added that to the conditional formatting in the Bar color part of the visual. 

Althoguh I see no error on the measure. I still see no real color changes in the bar chart. What could be wrong?

Both of the [Total Past Due] and [Average_TotalPastDue]) are columns in the table. Bar chart is a clustered bar chart.

[v-achippa]

Hi @Clockwise,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @danextian, @cengizhanarslan, @FBergamaschi and @Zanqueta for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[Clockwise]

The soluton did not work exactly but were useful. I was able to resolve it in a differnet way. This can be closed.

[danextian]

It is possible that there are more than one values of either [total past due] or [average_totalpastdue] column for each column in your visual. SELECTEDVALUE returns blank if  there isn't a single value in the current context. It would be easier for us to understand your use  case if you posted  a sample pbix, of course, with confidential data removed.

[cengizhanarslan]

1) Total Past Due (measure)

Total Past Due :=
SUM ( 'Summary'[Total Past Due] )

2) Average Total Past Due (measure)

If you mean “average across categories currently shown”, do:

Avg Past Due (by category) =
AVERAGEX (
    ALLSELECTED ( 'Summary'[Category] ),   -- replace with your axis field
    [Total Past Due]
)

 

3) Color measure (return hex is safest)

Bar Color PastDue =
IF ( [Total Past Due] > [Avg Past Due (by category)], "#00B050", "#C00000" )

4) Apply it to the chart

Format pane → Data colors → fx

• Format by: Field value

• Based on field: Bar Color PastDue

• Summarization: First

Now each bar should color based on whether it’s above/below the average.

[Clockwise]

Thanks cengiz for replying again. Correct me if I am wrong but sounds apart from the 1st and 2nd definition I have tried this. Yes, there is no category and I am doing for all the data.

Also if I do not add 

SELECTEDVALUE in front of columns name in the measure definition, I get the error of 

" A single value for **** can not be determined" which I am sure you are familiar with.  Maybe in your case you won't need it. 

Any thoght why it wokrs for Effects background but not for Bars? 

[cengizhanarslan]

That “single value cannot be determined” error is exactly why you should aggregate the column into measures.

Total Past Due =
SUM ( 'Summary'[Total Past Due] )

If Average_TotalPastDue is already a column with the same value repeated, still create a measure:

Average Past Due (overall) =
AVERAGE ( 'Summary'[Total Past Due] )

[FBergamaschi]

Hi @Clockwise 

can you share the file or a replica of it of some kind here or via private message? So we/I can check the actual pbix or a replica of it?

I think this could cut time short

Thanks

[Clockwise]

Unfortunately I can not shar ethe data. Unless I recreate another pbix. But if you have another thought please share. 

I also tried to change from clustered bar to stacked and column charts and no success. But it works great for the Background in Effects. I am wondering what is the issue with Columns or Bars colors that won't follow that same rule. 

[Zanqueta]

Hi @Clockwise ,  the issue is that the measure you created returns text values ("red" or "green"), while Power BI’s conditional formatting for bar colours requires either:

• Hex colour codes (e.g., #FF0000 for red), or
• Colour names recognised by Power BI, but only when applied correctly through a field-based rule.

Correct approach
You need to:

Ensure the measure returns a valid colour value (hex or recognised name).
Apply it under Data colours → Conditional formatting → Format by → Field value.

Here is an example of a corrected measure:

Bar_Color_PastDue =
VAR PastDue = SELECTEDVALUE('Summary'[Total Past Due])
VAR AvgPastDue = SELECTEDVALUE('Summary'[Average_TotalPastDue])
RETURN
IF(PastDue > AvgPastDue, "#FF0000", "#008000")  // Red or Green in HEX

Steps in the visual:

1. Go to Format → Data colours.
2. Turn on Conditional formatting.
3. Select Format by → Field value.
4. Choose the measure Bar_Color_PastDue.

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

[Clockwise]

I actually did use the conditional formatting by using field value originally  and that was the result. But in the Bar section of visual.  Is it somewhere else?  As it seems I do not see exactly what you put up there in Power BI desktop. Also I tried hex colors. Nothing. 

It sounds if Power BI recognizes colors as texts here, I can not tell it too look at as colors. 

So much reminds me of Excel when sometimes you can't tell Excel consider the format of column the way I want. 

[Clockwise]

Upon further checking, the conditional color measure works fine for background color, here is how I did this case,

Select visual, Format Visual -> General -> Effects -> Background.

So why it won't work for bar colors?

Here is how I get to bar colors, Format  Visual -> Visual -> Bars