Forum Discussion

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

Average Clients Per Hour, Per Month

Hi all - I have a very large dataset, spanning 10 years, where we havew mapped client trends over time.  I need to know how to work out the number of clients seen per hour, on average per month.  Each client contact is identified by 'Event Key' field and is unique.  The hour in which the contact was made is identified by 'Hour' field, and obviously the respective date this was made is 'Event Date'.

 

On a graph I need to show our average per hour, but for each month in the dataset.  I'd like to show another chart which simply shows average per houre, per event date (though this would look a bit more messy as a chart).

 

If you're able to show me the way, I'd be really grateful.  Sample dataset attached.  Many thanks!

Sample Data Client Average Per Hour 

Need Help

4 Replies

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

    Hi Creative_tree88 - To calculate average number of clients seen per hour per month, you can create a below dax measures

    Create a calculated column for the year and month & dateHour

     

    YearMonth = FORMAT([EventDate], "YYYY-MM")

    DateHour = FORMAT([EventDate], "YYYY-MM-DD HH")

     

    Average number of clients per hour for each month

     

    ClientsPerHourPerMonth =
    CALCULATE(
    COUNT(ClientContacts[EventKey]),
    ALLEXCEPT(ClientContacts, ClientContacts[YearMonth], ClientContacts[Hour])
    ) / DISTINCTCOUNT(ClientContacts[EventDate])

     

    Average number of clients per hour for each day

    ClientsPerHourPerDay =
    CALCULATE(
    COUNT(ClientContacts[EventKey]),
    ALLEXCEPT(ClientContacts, ClientContacts[EventDate], ClientContacts[Hour])
    ) / DISTINCTCOUNT(ClientContacts[EventDate])

     

     

    Hope it works!

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

     

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

    rajendraongole1  Thanks so much, but it doesn't look right, numbers look really high.  Let me know you what I need to achieve, with this sample extract:

     

    I have used this formula for working out how many clients are seen per hour, per day:

    A TEST = averagex(DISTINCT('Test Python Exam'[AD_Attend_Hr]), calculate(DISTINCTCOUNTNOBLANK('Test Python Exam'[AD_Event_Key])))

    However I need to somehow work out the AVERAGE number of clients seen across the whole month, per hour.  So you can see in the above example, what I need to show.

    In my actual data, just for Apr-14, this is what I'm getting using my formula above - it's working out the actual number of clients seen per day, in the month of Apr-14 as example.  However, you'll see the total at the bottom of 16.77 is simply the sum of those numbers, and what I need is the average, in this case it would be 1.28 clients per hour, over the course of Apr-14.  Obviously I have 10 years worth of data, so need to then show this over the course of the year on a chart.

    Hopefully this explains what I need a bit more?  



    Always appreciate your help, let me know if you'd like me to supply anything more to help understand this.