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...
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

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
Super 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

  • dunnno's avatar
    dunnno
    New Member
    4 years ago

    Sorry, i don't understand what you mean, i'm not an advanced user at all.

     

    I'm using a card visualisation that simply shows the result of the average calls a day :

     

    for example, since January 1st, the card visualisation shows

    56 calls/day

     

    I just want to have another card showing the same average, but number of calls a day Jim picks up :

    20 calls/day by Jim

    • goncalogeraldes's avatar
      goncalogeraldes
      Super User
      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

      • dunnno's avatar
        dunnno
        New Member
        4 years ago

        That works ! Thanks a bunch, thanks for your quick answer and solution 😄