Variable value not taken by chart

[DEMONIO_AZUL]

Hello there!

I have a bar chart that should look like this:

The average value is stored in a variable defined like this:

AverageSales = Avg({1<[Full Name]={*}>} Sales)

Note: [Full Name] is the name of the dimension I use for each Salesperson.

I use it to plot the Add-on Reference line, which has the correct value, 460.77.

To colour the bars, all bars that are above the average must be in green, all the rest are red.

It works with the numeric value in the Appearance section where I input the value, but it does not work otherwise. The variable does not work, neither the very same expression that defines the variable... but it does work on the Add-on Reference line. 

What am I doing wrong? 

I have tried all these, but only the numeric value works:

Any help will be appreciated.

Thank you!

[NoahF]

Hi @DEMONIO_AZUL ,

since your are ignoring selections in your Set Analysis anyways. You could also just calculate the average in the script beforehand and store it into a variable.

Avg_Table:
LOAD
    Avg(Sales) AS AverageSales
RESIDENT Sales_Table;

// Store the average in a variable
LET vAverageSales = Peek('AverageSales');

Drop table Avg_Table;

Then your can just use your average $(vAverageSales) in the color expression. 

Regards 

Noah

[MatheusC · Accepted solution]

Hi, @DEMONIO_AZUL 

Try using the total qualifier in your avg() expression

AverageSales = Avg(total  expression)

if(sum(Sales)>=$(AverageSales),color1,color2)

- Regards, Matheus

[DEMONIO_AZUL · Accepted solution]

Thank you @MatheusC . This was useful. The expression as you posted it was not working either, but this, with your idea, did the trick: