Forum Discussion

V24's avatar
V24
Icon for Helper I rankHelper I
2 years ago

Filtering visuals on a daily/ monthly basis

I have 5 columns: Process Name, no of robots, avg time for robots, date, time(aggregated for 3 hours). I have a stacked column chart with time on x-axis like this: 2:00:00,5:00:00,8:00:00,11:00:00 etc up to 23:00:00. Y-axis have the avg time for robots. So essentially the y axis- averages the avg robot time for the same time irrespective of the day/week.


I want to create 2 more visuals where the avg robot time is based on day ie it takes the avg per day for all the timestamps & the other avg per month. It averages the avg robot time for the 2:00:00,5;00;00,8:00:00, 23:00:00(these time stamps are constant on x-axis) the y-axis is dependent on the day/month.

Need Help

4 Replies

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

    Hi, you already have the measure it seems. Simply use dates in x-axis instead of time to show day wise average. Similarly use month in x-axis for month wise average. 

    • V24's avatar
      V24
      Icon for Helper I rankHelper I
      2 years ago

      Hi the timestamps have to be in the x-axis. Essentially I want to show what was the average handling time at 2:00:00 on a daily basis (ie taking the average of  robot handling time at 2:00:00 for  all days and for all the timestamps) similarly for months.

      • V24's avatar
        V24
        Icon for Helper I rankHelper I
        2 years ago

        For the months 2:00:00, 5:00:00 etc will be on the x-acis the avg time is calculated as follows: average of the total time take by robots on a monthly basis for 2:00:00,5:00:00 etc

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, V24 

     

    Create a new column with the daily average of the bot's time for each timestamp

     

    DailyAvg = AVERAGEX(
    FILTER('Table', 'Table'[Time] = EARLIER('Table'[Time]) && 'Table'[Date] = EARLIER('Table'[Date])),
    'Table'[avg time for robots]
)

     

    Then create a new column with the monthly average of the bot's time for each timestamp

     

    MonthlyAvg = AVERAGEX(
    FILTER('Table', 'Table'[Time] = EARLIER('Table'[Time]) && MONTH('Table'[Date]) = MONTH(EARLIER('Table'[Date]))),
    'Table'[avg time for robots]
)

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

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