Conditional Color Formatting with Measures in Bar Chart
Hey everyone!
I looked at similar topics but could not find an answer to my question.
We have a total of 21 branches. They were placed on the horizontal axis of the chart and, in the graphic, the ratio of collection/capacity made by the each branches in the last month was shown. I also turned on the average line in the chart.
Now I want to color the chart with;
-Red if the collection / capacity ratio for each branch is greater than the average,
-Yellow if it is between the average and half the average,
-And green if it is less.
I tried this expression: if((sum({<DATEFIELD={"$(=date(Addmonths(TODAY(),-1),'YYYY-MM'))"}>}COLLECTION))/sum( {<DATEFIELD={"$(=date(Addmonths(TODAY(),-1),'YYYY-MM'))"}>} CAPACITY)>=avg(total(Aggr{<DATEFIELD={"$(=Date(Addmonths(TODAY(),-1),'YYYY-MM'))"}>}COLLECTION)))/avg(total(Aggr(CAPACITY)),rgb('213','0','50'), if((sum({<DATEFIELD={"$(=date(Addmonths(TODAY(),-1),'YYYY-MM'))"}>}COLLECTION)/sum( {<YIL_AY={"$(=date(Addmonths(TODAY(),-1),'YYYY-MM'))"}>} CAPACITY)<=0.5*avg(total(Aggr{<DATEFIELD={"$(=Date(Addmonths(TODAY(),-1),'YYYY-MM'))"}>}COLLECTION)))/avg(total(Aggr(CAPACITY)),rgb('19','117','71'),rgb('249','194','19')))
