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

3 Replies

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.