Forum Discussion

dunnno's avatar
dunnno
New Member
4 years ago
Solved

Calculate an average value from a filtered column

I've got a "PickupThePhone" SP list in which users enter their name /when / what about / etc when they pick up the phone.

 

I'd like to be able to get an average per day / week a specific user (let's call him Jim) picked up the phone from the total phone calls we had since the beginning. 

 

I managed to get the average calls per day* since the beginning, but i'm clueless for that one explained above.

 

* 

Calls/Day =
AVERAGEX(
    KEEPFILTERS(VALUES('PickupThePhone'[Date])),
    CALCULATE(COUNTA('PickupThePhone'[ID]))
)
 
Need Help
  • goncalogeraldes's avatar
    goncalogeraldes
    4 years ago

    You could do with the following:

     

    Answered Calls =
VAR _total =
    CALCULATE ( COUNTA ( 'PickupThePhone'[ID] ) )
VAR _filtered =
    CALCULATE (
        AVERAGEX (
            KEEPFILTERS ( VALUES ( 'PickupThePhone'[Date] ) ),
            COUNTA ( 'PickupThePhone'[ID] )
        ),
        'PickupThePhone'[UserName] = "Jim"
    )
RETURN
    DIVIDE ( _filtered, _total, 0 )

     

    Or by adding the filter directly to the visual. Your choice! 🙂

     

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
    Thanks!

    You can also check out my LinkedIn!

    Best regards,
    Gonçalo Geraldes

6 Replies

  • goncalogeraldes's avatar
    goncalogeraldes
    Icon for Super User rankSuper User
    4 years ago

    Hello there dunnno ! You can try this:

     

    Answered Calls =
VAR _total =
    CALCULATE ( COUNTA ( 'PickupThePhone'[ID] ) )
VAR _filtered =
    CALCULATE (
        AVERAGEX (
            KEEPFILTERS ( VALUES ( 'PickupThePhone'[Date] ) ),
            COUNTA ( 'PickupThePhone'[ID] )
        ),
        'PickupThePhone'[UserID] = SELECTEDVALUE ( 'PickupThePhone'[UserID] )
    )
RETURN
    DIVIDE ( _filtered, _total, 0 )

     

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
    Thanks!

    You can also check out my LinkedIn!

    Best regards,
    Gonçalo Geraldes

    • dunnno's avatar
      dunnno
      New Member
      4 years ago

      That was quick :D, thanks !

       

      An average of how many calls a day or week Jim pick up :

       

      Can you say me where i write Jim's name in the formula ? i replace UserID by his name ?

       

      Does your formula average per day or per week ?

      • goncalogeraldes's avatar
        goncalogeraldes
        Icon for Super User rankSuper User
        4 years ago

        Hello there dunnno ! This measure is dynamic, meaning it averages depending on the aggregations fields you provide in the visual you use this measure in. 

         

        Say, for example, you use a matrix visual with the User's name in the row values: this measure will calculate for each user, its percentage of answered calls. When you add more fields to the rows, you are adding granularity to your information. So you can use either each day or each week as an aggregation!

         

        Hope this answer solves your problem!
        If you need any additional help please @ me in your reply.
        If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
        Thanks!

        You can also check out my LinkedIn!

        Best regards,
        Gonçalo Geraldes