Change the color of a bar chart depending on the amout of data points used

[Lumbisno]

Hello!  I have a barchart where each bar is an averaged measurement. I'm trying to make the bars appear a different colour if the amount of data points used for this average are few (< 3 for example).

This way I can visually distinguish possible low-trust measurements which are not based in a lot of data.

Is there any way to do this? Thanks in advance

[Anonymous]

Hi, @Lumbisno 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yongkang Hua

[Ritaf1983]

Hi @Lumbisno 
You can use a measure like :

color =

var count_points= COUNTROWS('Table')

Return

if (count_points<3, "red","grey")

(the colos can be used as a xeca codes too)

After you have this measure you can use it as a condition for bar color formatting:

Result :

I used a subtitle for a legend.
PBIX is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Lumbisno]

Hello! thank you for your reply and your help. Unfortunately I think because of how my data is formatted, this way does not work. The problem I have is detailed in my answer to Freginier. Is there any way to work around it?

[Ritaf1983]

Hi @Lumbisno 
Apologies, but I didn't understand how the data structure you provided differs from the one I demonstrated...
Did you download the PBIX attached?
Anyway, I replaced my data with yours, and it seems that the result is exactly what you requested..

The updated pbix is also attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Lumbisno]

I'm sorry, I did not explain myself properly. The reason I created different measures is because there are different base colours for the bars ( see image below).

So I would need for the bars to each keep the original colour or to change to that "low trust" colour.
Thank you very much for your help and I apologise for the confusion.

[freginier]

Hey there!

Power BI allows you to conditionally format bar colors using DAX and field-based conditional formatting.

First you need to create a measure that counts the number of data points used for each bar: 

DataPointCount = COUNT('Table'[Value])

Next you can create a measure that assigns a color code based on the number of data points:

BarColor =
VAR CountDataPoints = [DataPointCount]
RETURN
IF( CountDataPoints < 3, "Red", "Blue" )

Finally, apply Conditional Formatting in the Bar Chart: 

1. Select your bar chart in Power BI.
2. Click on Format (Paint Roller) → Columns.
3. Find the "Color" option and click the fx (conditional formatting) button.
4. In the "Based on field" dropdown, select the "BarColor" measure.
5. Set the formatting to match Red & Blue accordingly.

Hope this helps!

😁😁

[Lumbisno]

Hi! thanks you very much for the help! I got it started but I ran into the following problem:
My data set looks like this:

A	1
A	5
B	2
C	4
B	7
D	1
A	3
B	5

Each bar in my chart is an average of the second column grouped by the first column, which we could call the "vendors" (A,B,C or D). For example ,in this data set the bar for the averaged values corresponding to the vendors "C" and "D" should appear a different colour because they have very few data points.
With your solution as i understood it I made 4 separate count measures and for each one a bar color measure.
Unfortunately it appears that the conditional formatting for the bars applies a single rule to all bars, while in my case each bar has to look to it's own rule. Do you know any way to work around this? thank you again!