How to colour an expression by the desired dimension?

[alespooletto]

I have an expression that takes in a measure, and according to its value, gives me a colour for the columns if its value is above or below the average.

if(
Count({<ACTION_CODE={'COMPLETE'}, MainSFC={'Main SFC'}>} DISTINCT SFC) / Count(DISTINCT WorkingDay)
<
Avg(TOTAL Aggr(Count({<ACTION_CODE={'COMPLETE'}, MainSFC={'Main SFC'}>} DISTINCT SFC) / Count(DISTINCT WorkingDay), MainSFC)),
RGB(255, 0, 0), // Red color if below the average
RGB(0, 0, 255) // Blue color if above the average
)

The problem is, this is not giving me the correct results. Only in few cases does it work, but other cases when the value is below the mean, it still doesn't get coloured red.

[TcnCunha_M]

could you print what happening because just look the expression I can't see nothing wrong

[alespooletto]

Yes， thank you. I added the information

[Anil_Babu_Samineni · Accepted solution]

Always pause conditions and use table object to see. 

if(
Count({<ACTION_CODE={'COMPLETE'}, MainSFC={'Main SFC'}>} DISTINCT SFC) / Count(DISTINCT WorkingDay)
<
Avg(TOTAL Aggr(Count({<ACTION_CODE={'COMPLETE'}, MainSFC={'Main SFC'}>} DISTINCT SFC) / Count(DISTINCT WorkingDay), MainSFC)),
1, // Red color if below the average
2 // Blue color if above the average
)

[TcnCunha_M · Accepted solution]

simple question, where are you adding this conditional to calculate ?

I understand you want to put red all the cases where they are going bellow the trend line, 
this is trend line ?

 Avg(TOTAL Aggr(Count({<ACTION_CODE={'COMPLETE'}, MainSFC={'Main SFC'}>} DISTINCT SFC) / Count(DISTINCT WorkingDay), MainSFC)

and could make both are master items ? to make live easy to make If (Master_ITEM_1 < mASTER_ITEM_2 , RED(),BLUE())

try like this

[alespooletto]

So this is a good idea, but unfortunately it's really the second measure that doesn't seem to work out well.  I have to work and improve it to make it functional.

[TcnCunha_M]

OK 

I see so you have a problem with your expression

I may suggest :

Count( TOTAL  {<ACTION_CODE={'COMPLETE'}, MainSFC={'Main SFC'}>} DISTINCT SFC) / Count(WorkingDay)

I believe this should be your total scenario or something like this.

since there is a problem with your expression I believe the answer from Anil or my you should mark as right awnser

[alespooletto]

Yes, thank you. I was able to solve the expression mistake, and it's thanks to both of you pointing out ways to find this out. 

However @TcnCunha_M I still have an issue, as when I place the measures inside, I expect to be able to compare them all with the maximum average that I get. For example here: 

I want to compare all of those rows against the 191 at the top. The current formula of the second column is :

Count( {< ACTION_CODE = {'COMPLETE'}, MainSFC = {'Main SFC'}>} distinct SFC) / Count(Distinct WorkingDay)

And instead of a breakdown by every row, I want it to always output its total. Can you help me figure this out? It seems like using Sum(All) and Sum(Total) it doesn't seem to work. 

[TcnCunha_M]

you probably need to aggregate by the  dimensions and use total like 

Sum ( TOTAL

Aggr(
Count( {< ACTION_CODE = {'COMPLETE'}, MainSFC = {'Main SFC'}>} distinct SFC) / Count(Distinct WorkingDay)
 Fields )

)m