Dynamic Bar color

[#1]

I have a sales table which has 2 columns of total amount and selling agent. I also have a diagram which shows agents and their sum of selling.

I want to put an expression in background color which:

if the agent has sold more than average of all agents, makes the bar green. Otherwise, make it red (Regardless of active filter).

=If(Avg({1}[Selling Price])>> [Selling Price], RGB(128,255,0), RGB(255,0,0))

my changes the color when I change the current selection (filter).

any suggestion?

[sunilkumarqv]

@hi Sep

As my understanding

you point is -if the agent has sold more than average of all agents, makes the bar green. Otherwise, make it red (Regardless of active filter).

If(agent>Avg{1[Selling Price]},Green,red)

share sample data for required o/p

[#3]

thanks sunil

Exactly as you explained. e.g. I want to see if Martin has sold more than average of all agents change the bar to green otherwise red like brad. But as you can see my expression doesn't respond properly.

if(Avg({1}[Selling Price])>>[Selling Price], RGB(255,0,0), RGB(128,255,0))

[santhoo_san]

you may have to use the calculation in reverse order ie., If selling price > avg {1} selling price, green(), red().

[mrossoit]

if(Avg(TOTAL [Selling Price])>[Selling Price], RGB(255,0,0), RGB(128,255,0))

[#6]

Hey,

this is an example  of  how I did it then try to do like i did maybe it can work for you too

you can replace Sum(#) and 25000 with your expressions 

[antoniotiman]

Hi Sep,

You can Use RangeAvg() Function

If(RangeAvg(Top([Selling Price],1,NoOfRows())) > [Selling Price],Red(),Green())

or Sum([Selling Price]) if Your expression is Sum([Selling Price])

Regards,

Antonio

[#8]

The thing is I want the qlikview to calculate the average. Take a look at this formula:
If(Sum({1} [Selling Price]) > (Sum({1}[Selling Price])/Count({1}[Agent Name])), RGB(0,0,0), RGB(255,255,255))
what is it I'm missing?!

[#9]

Antonio Mancini it's not working. Can you please point out what is the problem with above expression.

[#10]

Hi. Nope I want to calculate average selling by agents, Like following expression:

If(Sum({1} [Selling Price]) > (Sum({1}[Selling Price])/Count({1}[Agent Name])), RGB(0,0,0), RGB(255,255,255))