Forum Discussion

Creative_tree88's avatar
Creative_tree88
Icon for Helper V rankHelper V
2 years ago

Average Clients Per Hour, over a monthly timeframe

Hi all - trying to calculate the AVERAGE number of clients seen, per hour, over the course of a monthly timeframe (also yearly if possible).

 

I've attached a sample PBIX for you to help if possible, and show what I expect a little more clearly.  I've already used a formula which calculated the average number of clients on a daily basis (see PBIX attached), but I am lost as to then work out how this translates monthly, and if possible yearly.

 

Your help is much appreciated, as always.

 

Sample PBIX 

Need Help

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Turn on Date/Time intelligence and let Power BI create a 'date dimension' with Year and Month columns
    Go to File menu/select Options and go to Current file/Data Load

     

  • Creative_tree88's avatar
    Creative_tree88
    Icon for Helper V rankHelper V
    2 years ago

    Anonymous Thanks.  However, this is not what I need.  I need the average per hour of clients seen, when taken across the whole month.  The figure of 16.77 for Apr-14 is not the average per hour for Apr-14, the average per hour is 1.28.  So, for the whole of April-14, we saw (on average) 1.28 clients per hour.  This is what I need to work out.

    Thanks for your time.

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      There are a couple of fallacies at play here.  First off - months do not have the same number of days.  Then you are not considering weekends or holidays, as well as workdays shifting across time. And finally you are ignoring business hour ranges.

       

      What insights and actions are you trying to get from this report?

      • Creative_tree88's avatar
        Creative_tree88
        Icon for Helper V rankHelper V
        2 years ago

        lbendlin My business is a call centre, we 'see / talk to' clients on a 24 / 7 basis, even bank holidays, Xmas etc.  It's a round the clock business.

        We want to look at our productivity per hour in terms of no. clients seen / spoken to, as new technology needs to be evaluated in business cases to determine whether they have helped in our efficiency or not.  Hence looking at average per hour.

         

        As already detailed, I know how many clients, on average, we see / talk to per day.  That's a done deal with the formula 'A Test' in my sample data.

         

        I accept each month has a different number of days, but when looking at Apr-14 numbers, you can see the total at the bottom is NOT the average per hour, over the course of the month.  The average in this respect should be 1.28 clients per hour for Apr-14.  

         

        I need a formula, to be able to calculate this - hence my question and sample data.  It seems it's a tricky one!  Once I have a formula I can apply it to the 10 years of data we have, and understand how, at various points in history when we have invested in new technology, or had a recruitment drive, the productivity / efficiency has increased (or not).

         

        Thanks for your help.