Forum Discussion

Creative_tree88's avatar
Creative_tree88
Helper V
2 years ago
Solved

Average Clients Per Hour

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.  Eac...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Creative_tree88
Helper V
2 years ago

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.