change color for clustered chart when values become +ve and -ve

[GAntony_1409]

Hi,
I need support in how I can have the color of a particular bar in a clustered column chart change when the value becomes positive & negative.
For ex : for the 5th bar(R) in the image, when its -ve to show in red and when its postive to show in orange.
the 5th bar (R) is the difference between light brown(LB) and light green bar(LG)
i.e, LB-LG = R

Now it shows Red all the time.

[amitchandak]

@GAntony_1409 , what is becoming +ive and -ive here. If you are using one measure and no legend in the visual you can create a color measure and use that under conditional formatting with "Field option"

example

if(FIRSTNONBLANK(Table[Value],"true")= "true","green","red")

if(FIRSTNONBLANK(date[date],blank())= tofay(),"green","red")
if(max(date[date])= tofay(),"green","red")

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[GAntony_1409]

Hi Amit,

I was able to fix this issue by using a measure (If function).

Thank you

[Zuzu]

Hello @GAntony_1409 ,

  If you have found a solution could please share the if condition you used.. I am in the same boat right now where 1 bar in a clustered column chart needs to change color depending on a value.. But I'm having no luck yet. I'd really appreciate  it. Thank you

[GAntony_1409]

Hi @Zuzu,

There is no direct  solution available to change the color based on +ve and -ve values for clustered bar chart.

Alternate Solution : By using a IF statement you will get the bars separetly one when the value is +ve and one when the value is -ve

so what I have done is I have used two separate  IF functions to get +ve  & -ve value

For Example : let say there are two columns of data with header name as   "Buy" , "Sold" in Sheet1

and column named "Result" is the difference between "Buy" & "Sold" (Buy - Sold) which is the clustered bar which you need to change the color as per the value.

If Buy - Sold is a positive value I name this measure as "Profit"

If Buy - Sold is a negative value I name this measure as "Loss"

Now the IF function for the measure Profit and Loss is as below.

Profit = 

IF(SUM(Sheet1[Buy])-SUM(Sheet1[Sold])>0,SUM(Sheet1[Buy])-SUM(Sheet1[Sold]),"")

Loss =

IF(SUM(Sheet1[Buy])-SUM(Sheet1[Sold])<0,SUM(Sheet1[Buy])-SUM(Sheet1[Sold]),"")

after the above two measures are added you can deselect the "Result" field which does not change the colur and now include the fields Profit and Loss and assign different colors.

[GAntony_1409]

Hi @amitchandak ,

Thank you for your quick reply appreciated.

Is there no direct formatting changes that can be applied in Power BI as how it is available in excel without using the mentioned formula.

I am looking for a similar function if it is available in Power BI.

[V-lianl-msft · Accepted solution]

Hi @GAntony_1409 ,

Unfortunately, there is no such feature for the time being.

You could submit your idea to help improve power bi.

You can try to create a measure and apply it in a conditional formatting.

It is important to note that when you add content to the legend field in the graph, you will not be able to apply conditional formatting.

https://chandoo.org/wp/conditionally-format-visuals-in-power-bi/ 

If the problem persists,could you share the sample pbix or sample data?(Please mask any sensitive data before uploading)

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

[GAntony_1409]

Thank you for your response, I find this idea has been submitted several times from 2014 onwards.

I shall provide a sample data.