Averages with small multiples charts

[LeoQ]

Hi everybody! I have been trying to solve this for days, but I couldn't find a solution.
I have a measure that calculates the average amount of mails answered by agents for day. With that information I created a small multiples chart, as shown in the image below. There I show how many mails each agent answered the last days to compare.

What I need is to apply a conditional color to each column, according to wether the mails answered by each agent that day, is above or below the average of mail answered by agent that day.
For example the average number of mails annswered by agent the 27th was 11,09, so the columns of each agent for that day should be red if the number is lower than 11,09 or green if it's greater.

I tried with calculated tables and other kind of measures, but they always filter by agent in each bar chart and doesn´t take the total average for day.

Thanks a lot!

[Anonymous · Accepted solution]

Hi @LeoQ ,

You can create a measure as below to get it, please find all the details in the attachment.

Conditonal formatting = 
VAR _averageofperday =
    CALCULATE (
        AVERAGE ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] )
        )
    )
VAR _averageofperagent =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] )
                && 'Table'[AGENT] = SELECTEDVALUE ( 'Table'[AGENT] )
        )
    )
RETURN
    IF ( _averageofperagent < _averageofperday, "red", "green" )

Best Regards

[LeoQ]

It worked perfectly. Thanks a lot for your time!!

[mahoneypat]

You likely need an expression like this for your bar color.

BarColor =
VAR vThisAgent = [YourAgentDayAvgMeasure]
VAR vAllAgents =
    CALCULATE ( [YourAgentDayAvgMeasure], ALLSELECTED ( Table[Agent] ) )
RETURN
    IF ( vThisAgent > vAllAgents, "Green", "Red" )

Pat

[LeoQ]

Hi, thanks for your time. The problem is that I need to compare the absolute amount of mails answered per day per agent, but compare it with the average of all agents in the same day. I tried your formula, but it color all the columns the same.