Average Clients Per Hour

[Creative_tree88]

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 

[Anonymous]

Hi @Creative_tree88 ,

Create measure.

Average Clients Per Hour Per Month = 
VAR _sumhour =
    CALCULATE (
        SUM ( 'Sample Data'[Hour] ),
        FILTER (
            ALL ( 'Sample Data' ),
            'Sample Data'[Event Date].[Month] = MAX ( 'Sample Data'[Event Date].[Month] )
                && 'Sample Data'[Event Date].[Year] = MAX ( 'Sample Data'[Event Date].[Year] )
        )
    )
VAR _count_clients =
    CALCULATE (
        DISTINCTCOUNT ( 'Sample Data'[Event Key] ),
        FILTER (
            ALL ( 'Sample Data' ),
            'Sample Data'[Event Date].[Month] = MAX ( 'Sample Data'[Event Date].[Month] )
                && 'Sample Data'[Event Date].[Year] = MAX ( 'Sample Data'[Event Date].[Year] )
        )
    )
RETURN
    DIVIDE ( _count_clients, _sumhour )

Average Clients Per Hour Per Day = 
VAR _sumhour =
    CALCULATE (
        SUM ( 'Sample Data'[Hour] ),
        FILTER (
            ALL ( 'Sample Data' ),
            'Sample Data'[Event Date] = MAX('Sample Data'[Event Date])
        )
    )
VAR _count_clients =
    CALCULATE (
        DISTINCTCOUNT ( 'Sample Data'[Event Key] ),
        FILTER (
            ALL ( 'Sample Data' ),
            'Sample Data'[Event Date] = MAX('Sample Data'[Event Date])
        )
    )
RETURN
    DIVIDE ( _count_clients, _sumhour )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Creative_tree88]

@Anonymous Thanks for your reply, much appreciated.  The numbers for average per day are really low.  I've used this formula:

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

which gives me the actual numbers of clients seen per hour, per day.  This works well to show ACTUAL numbers of clients seen per day. 

HOWEVER, I then need to find a way to show this as an average over the month - this is what I am getting back from my actual dataset, just to show you, on a daily basis (AD_Event_DT is my actual date field in my dataset).

If I was to try and plot this using my Month field of AD_Cal_Mth, it wants to obviosuly plot 16.77 for Apr-2014, when what I NEED is the average across this month i.e. 1.28 average for month of Apr-2014.

If I can do this, I can plot all 10 years of my data using this principle, just not sure how to do it.  Really appreciate your help if you can crack this one.

[Anonymous · Accepted solution]

Hi @Creative_tree88 ,

You may be talking about matrix totals, where you can use functions such as HASONEFILTER or ISINSCOPE to perform different calculations for the total row.

In this case, you can create an expression to get the average value for April 2014 and then return it in the total hierarchy.

For more details, please refer to the link:

Dealing with Measure Totals - Microsoft Fabric Community

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.