I'm having a problem presenting a chart using an expression for the column colour.
I have a bar chart that presents financial data that is broken down in various ways but mainly department, team, spend, budget and one of the views is Spend v Budget.
I work this out using calculation Sum (Costs)-((Sum (Budget))/12*Count (DISTINCT Month)), then the expression used to display the chart colour is if(($(vVar))>=0,Green(),Red()) where vVar = Sum (Costs)-((Sum (Budget))/12*Count (DISTINCT Month)).
When you choose a team from a list box the chart will show green or red depending on the result of the selection so in that case it works fine.
The problem comes where the chart is shown with no team selected and is shown as departments - all bars, whether in the positive or negative financially show as red because the total variance for all budgets is a negative value.
Is there a simple way to show all fiigures above 0 in a chart as green and all those beneath 0 as red - or another way round this. If i'm not clear please let me know.
Thanks for your response, but thats what i've already done. I don't think i was clear in my OP.
That works exactly as it should, showing positive values as green and negative values as red - but only when a selection is made in a list box.
When the chart is showing the data with no selections in the list box all values show as red, even those that are a positive number. This is because Sum (Costs)-((Sum (Budget))/12*Count (DISTINCT Month)) is a negative value overall.
I need for the chart to show positive values as green and negative values as red even when no selection is made. I'm not sure if there is an easy way to do this in the chart properties, if I'm looking at variable to define variation per team without selections in list boxes or some other way?
If you go to the expression and expand the +, you will see options to add formulae for various attributes of the expression. Select the background colour and enter the expression that you have placed in the colours tab in the dialogue box on the right.