Daily average by month

Hi, 

I have a calculated field that I can currently showing by month in a bar chart. 

I want to be able to show the daily average over the month by month. what formula would I need to use to do this? 

for example:

1st may = 30

2nd may = 20
3rd may = 10

total by month would be: 60 

average for that month would be: 20 

I want to show the average for the month, monthly. but when I do that it just shows the total by month, even if I use the average keyword... 

any idea? 

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Have you tried doing an inline sub-query? Thinking that might keep your daily data but also grab the monthly value for you.

    Reply Like 2
  • Hi Callie Cobbs , I managed to get this working, just by dividing the sum of one column by the sum of the other... not entirely sure how it worked, but it did 馃檪

    thank you for your suggestion

    Reply Like
  • I would sum the total as numerator and the count days as the denominator 

    Reply Like 1
  • Damian Waldron this is similar to what I did. 

    we give weekdays and weekends different importance, so instead of counting the days, I gave each day its own value and then summed it to get the denominator. 

    Reply Like 1
  • Great solution. This design pattern is very useful for semi additive numbers. I.e. sum on all dimensions except date. Then if you change the grain of the date from say daily to weekly, monthly, yearly etc you will get an appropriate average over time. I.e. Daily will be sum(total)/1, weekly will be sum(total)/7 etc.

    Reply Like 1
Like1 Follow
  • Status Answered
  • 1 Likes
  • 1 mth agoLast active
  • 5Replies
  • 29Views
  • 3 Following