How to color negative number in red in stacked bar chart

[DataNinja777]

Hi Power BI Community,

I am trying to conditionally format the color of the stacked bar chart by coloring negative numbers in red and postive numbers in green.  I am trying to change the blue circled fields to Min and Number, but when I save them they get changed to the default value.  I'd greatly appreciate it if anyone could let me know how to apply conditional formatting.  

Thank you for your help.  

[Anonymous]

Hi @DataNinja777 ,

I tested the solution provided by @danextian  and it outputs the results correctly. You mentioned that an error occurs when you modify the measure, can you provide details of the error message and the expected result?

Best Regards,

Neeko Tang

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

[DataNinja777]

Hi @Anonymous 

Thank you for your sample file, too.  Your solution is exactly what I am after, however, when I tried to do the same in my data model, using exactly the same visual, same color measure and the same conditional formatting configuration, I get the visual like below, which is not respecting the color conditional formatting.  

I'd greatly appreciate it if you could shed some light as to why this is happening in my pbix visualization.  

Thank you for your help!

[Anonymous]

Hi @DataNinja777 ,

Can you please share more details to help us clarify your scenario?
Please share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Neeko Tang

[DataNinja777]

Hi @Anonymous 

Thanks for your help.  When I created a simple sum measure, it worked in other visual within the same pbix file but in this particular measure visualization, it is not working.  The measure I am trying to using this conditional formatting is as follows:

Unfortunately, I cannot share the pbix file, and it takes time to annonimize the data, so I am trying to find a solution by investigating the measure which may be causing this issue.  

Thank you for your help!

[Anonymous]

Hi @DataNinja777 ,

I tested your measure and the conditional format still displays correctly.
You can convert the bar chart to a table visual to see if the measure outputs correctly.

Then the result is as follows.

Best Regards,

Neeko Tang

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

[DataNinja777]

Thank you @Anonymous for your help.  

In fact, when I changed the y-axis dimension to GL account, the color was as designed and intended.  However, the issue still remains if I use mmm-yyyy as the dimension.  I am not sure why when time dimension is used the coloring doesn't behave in the way intended.  I guess there is some difficult dax filter condition or something like that is involved, but I am not sure how to address this issue.  Do you think if I change measures to respect the filtering of mmm-yyyy, the coloring will behave in the intended way?  Thanks a lot for your help.

[Anonymous]

Hi @DataNinja777 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please feel free to let me know.

Best Regards,

Neeko Tang

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

[DataNinja777]

Hi @Anonymous 

Thanks for your continued follow up on this issue.  I've created a dummy data and tested the simplified version of my issue I am facing.  Doing this clarified my issue further, and I noticed that when I use the numerical field in the 2nd table and try to use that as the filter in the 1st visual which I want to color the negative value as red, it doesn't behave in the intended way.  If I don't use the 2nd chart to filter the 1st chart, the solution which @danextian and @Anonymous provided works in the way intended.  

The issue with this chart is, 

- The trial balance adds up to zero at every time dimension field

- The trial balance should not be added across time dimension and only added within a particular time point (therefore, max of the calendar table date was used)

- When I put the trial balance measure in a visual without specifying time field, it shows blank value due to it always adding up to zero.

- When I put the visual without GL account category, it also shows zero due to it adding up to zero all the time

Because of this, I've created a 2nd chart which specifies the category and the amount at the max date, but with the combination of these two charts, the 1st chart no longer respects the coloring formula in the Bars section.  

Thanks for your help.  

[DataNinja777]

Hi @Anonymous ,

Thanks for your message.  The issue hasn't been resolved.  While the visual behaves in the expected way when I use the GL account as the Y-axis, when I use yyyy-mm as the Y-axis dimension, it doesn't behave in the expected manner.  I will investigate the reason for this.  I think it could be due to the filtering of yyyy-mm.

[Anonymous]

Hi @DataNinja777 ,

I'm not quite sure what you mean. In my output, the y-axis is using a date field and he is coloring normally.

You can convert the bar chart to a table visual to see if the measure outputs correctly. Or provide a sample pbix.(Careful protection of data privacy)

Best Regards,

Neeko Tang

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

[Musadev]

Hi @DataNinja777 
Check the screenshot and the defined rules.

[DataNinja777]

Hi @Musadev 

Thanks, I did the same as your screenshot, but that didn't produce the color I am trying to show.  

[Rahul_2645]

Hi,
You can try this approach.

[DataNinja777]

Hi @Rahul_2645 

Thanks, and I have been trying to do the same as your configuration for a while, but when I save it, like below, 

The next time, when I open the configuration screen comes up like below:

and the color is not changed to the required coloring.  

[danextian · Accepted solution]

Hi @DataNinja777 ,

You can use a measure that checks whether another measure's value is positive or negative and returns a color based on a condition. This is a sample:

Text Color = 
IF ( [Sum of Numbers] < 0, "Red", "Green" )
//the color can be replaced with hexadecimal codes like #6B2328

Please see attached sample pbix

[DataNinja777]

Hi @danextian 

It appears that the Text Color measure is disregarding the filter applied to the stacked bar chart visualization, i.e., it is coloring based on the the total of the measure without taking into account of the filters applied such as yyyy-mm used in the Y-axis.  How can I change the text color measure or the bar color conditional formatting so that the colors respect the filters being applied to the visualization, such as yyy-mm in Y-axis?  

[DataNinja777]

Hi @danextian 

Thanks for your input.  I tried to do like below, but and I am trying to change the color of the bar chart depending on the sign of the value, but it is not having the impact.  When I remove the "" from the color code, the formula shows error.  How can I use the color code in this formula?