Sum of distinct count of an expression traffic light style graph

[shonarach]

I am trying to use a traffic light system in  chart

So far I have created the graph with the distinct count of the items, i have also created an average count which also works.

But I am looking for help to create a total of the average count of items so that I can change the bar colour to green if it is below the total average, red if it is above total average, otherwise colour it orange. 

I have tried many different combinations of avg / aggr/ total but I cannot seem to find the solution. 

Has anyone solved this?

[rubenmarin · Accepted solution]

Hi, it will better if you can upload a sample to play with.

It should be something like: Avg(TOTAL Aggr(Expression, Dimension))

Or add a variable that calculates the average with just (it's important to keep the initial '=' in variable value): =Avg(Aggr(Expression, dimensions)

And use in expression like: If(Expression>$(AvgVariableName), ...)

[shonarach]

Hi Ruben, 

The solution of Avg(TOTAL Aggr(Expression, Dimension)) solved my issue. 

Many thanks, 

Shona