Forum Discussion

LeoQ's avatar
LeoQ
Icon for Helper I rankHelper I
5 years ago
Solved

Averages with small multiples charts

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!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      LeoQ
      Icon for Helper I rankHelper I
      5 years ago

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

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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's avatar
      LeoQ
      Icon for Helper I rankHelper I
      5 years ago

      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.