Forum Discussion
Conditional Formatting a Bar Chart using a measure
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
The soluton did not work exactly but were useful. I was able to resolve it in a differnet way. This can be closed.
12 Replies
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.
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 HEXSteps in the visual:
- Go to Format → Data colours.
- Turn on Conditional formatting.
- Select Format by → Field value.
- 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 🌀.
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.
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
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
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.
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.
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?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] )
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
