# Kibana - aggregation in graphs (average number of things per bucket) **URL:** \ **Category:** Kibana\ **Created:** [August 3, 2022, 10:29am UTC](https://discuss.elastic.co/t/kibana-aggregation-in-graphs-average-number-of-things-per-bucket/311291 "2022-08-03T10:29:01Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![RonGros](https://avatars.discourse-cdn.com/v4/letter/r/e9a140/32.png) [@RonGros](https://discuss.elastic.co/u/RonGros)\ **Post date:** [August 3, 2022, 10:29am UTC](https://discuss.elastic.co/t/kibana-aggregation-in-graphs-average-number-of-things-per-bucket/311291/1 "2022-08-03T10:29:01Z")
Hi, I have a situation and I am stuck, I think what I want to do is not currently achievable but I hope I'm wrong. Let's assume I have the following data about people going to the movies: - Name of person - Which movies they watched - Timestamp of when the movie played - Whatever other information (sitting information, payment etc.) Now I want to know what is the average people that went to see movies at a certain date So I create a graph, and the horizontal axis will be a timestamp. but now my only option is - Either just show the count of people, regardless of movies, or I can breakdown by movies, but then I will see each movie individually and will not have a graph of the average. If I use a table - I can see the individual movies, and also a bottom line saying what the average number people was, but I can't find a way to translate it into graphs. I hoped that I can use functions for that, but there is no aggregation option in functions Is there a way to do that? am I missing anything? ---
**Author:** ![devon.thomson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/devon.thomson/32/85468_2.png) [@devon.thomson](https://discuss.elastic.co/u/devon.thomson)\ **Post date:** [August 3, 2022, 4:23pm UTC](https://discuss.elastic.co/t/kibana-aggregation-in-graphs-average-number-of-things-per-bucket/311291/2 "2022-08-03T16:23:09Z")
Hi @RonGros , is your data [denormalized](https://discuss.elastic.co/t/denormalized-data-vs-joins/181041)? If it is denormalized, I.E each row represents one `time` one `person` watched one `movie`, you can easily bucket by date in [Lens](https://www.elastic.co/guide/en/kibana/current/lens.html), then unique count of `name` would be the number of people who saw a movie in that time period. If you're calculating an average, you'd need to calculate it over multiple buckets, whereas a graph would show a value _per_ bucket, so I'm not exactly sure what the average you're looking for is. ---
**Author:** ![RonGros](https://avatars.discourse-cdn.com/v4/letter/r/e9a140/32.png) [@RonGros](https://discuss.elastic.co/u/RonGros)\ **Post date:** [August 4, 2022, 10:03am UTC](https://discuss.elastic.co/t/kibana-aggregation-in-graphs-average-number-of-things-per-bucket/311291/3 "2022-08-04T10:03:56Z")
Hi @devon.thomson ! yes, my data is denormalized, exactly like you described it. what I want to know is a single number (per day) telling me - what is the average number of people to watch any movie, without going into specific movies. it is easy to see the average number of people going to movie X or Y, but what I want is - what is the average number of people in movies as a whole, and I want a single number. So for example, if I had 4 movies, and on August 1st I have 2 record for movie 1, 10 records for movie 2, and 6 records for movies 3+4, I want something to tell me that the average for August 1st is 6 ---
**Author:** ![devon.thomson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/devon.thomson/32/85468_2.png) [@devon.thomson](https://discuss.elastic.co/u/devon.thomson)\ **Post date:** [August 4, 2022, 2:43pm UTC](https://discuss.elastic.co/t/kibana-aggregation-in-graphs-average-number-of-things-per-bucket/311291/4 "2022-08-04T14:43:26Z")
Great, thanks for the context! That makes sense. It seems like you can get this average by dividing the total number of unique people on the day by the total number of movies shown that day. You should check out [Lens formulas](https://www.elastic.co/guide/en/kibana/current/lens.html#lens-formulas)! ---
**Author:** ![RonGros](https://avatars.discourse-cdn.com/v4/letter/r/e9a140/32.png) [@RonGros](https://discuss.elastic.co/u/RonGros)\ **Post date:** [August 4, 2022, 7:03pm UTC](https://discuss.elastic.co/t/kibana-aggregation-in-graphs-average-number-of-things-per-bucket/311291/5 "2022-08-04T19:03:09Z")
@devon.thomson you're right! that's actually quite simple, not sure why I haven't thought of it, thanks! (BTW I Am well aware of the lens formulas) Now here is a bigger challenge - how do I find the maximum number of people that attended a movie at the same day...? ---
**Author:** ![RonGros](https://avatars.discourse-cdn.com/v4/letter/r/e9a140/32.png) [@RonGros](https://discuss.elastic.co/u/RonGros)\ **Post date:** [August 11, 2022, 12:07pm UTC](https://discuss.elastic.co/t/kibana-aggregation-in-graphs-average-number-of-things-per-bucket/311291/6 "2022-08-11T12:07:13Z")
@devon.thomson any idea? ---
**Author:** ![devon.thomson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/devon.thomson/32/85468_2.png) [@devon.thomson](https://discuss.elastic.co/u/devon.thomson)\ **Post date:** [August 11, 2022, 4:15pm UTC](https://discuss.elastic.co/t/kibana-aggregation-in-graphs-average-number-of-things-per-bucket/311291/7 "2022-08-11T16:15:09Z")
Hey @RonGros, If I was going about this, I would change the data to have a parsed `dayOfScreening` field made from rounding the timestamp to the day. You could do that at ingest time, which is how I would do it, or you could create a [scripted field](https://www.elastic.co/blog/using-painless-kibana-scripted-fields). [Here](https://discuss.elastic.co/t/help-in-parsing-date-from-timestamp-using-painless/157233) is an example of something similar being done. In my opinion, with elasticsearch it is _always_ better to think your data problems through from the ingestion side, even if it results in repeated information. ---
**Author:** ![RonGros](https://avatars.discourse-cdn.com/v4/letter/r/e9a140/32.png) [@RonGros](https://discuss.elastic.co/u/RonGros)\ **Post date:** [August 16, 2022, 10:57am UTC](https://discuss.elastic.co/t/kibana-aggregation-in-graphs-average-number-of-things-per-bucket/311291/8 "2022-08-16T10:57:53Z")
Hi Devon, I already have such a field, but how will that help me? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 13, 2022, 10:58am UTC](https://discuss.elastic.co/t/kibana-aggregation-in-graphs-average-number-of-things-per-bucket/311291/9 "2022-09-13T10:58:54Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.