# Kibana visualization - Average of sum of grouped values **URL:** \ **Category:** Kibana\ **Tags:** vega, visualisation\ **Created:** [November 30, 2023, 10:59am UTC](https://discuss.elastic.co/t/kibana-visualization-average-of-sum-of-grouped-values/348326 "2023-11-30T10:59:51Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Gianfranco\_Demarco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gianfranco_demarco/32/127563_2.png) [@Gianfranco\_Demarco](https://discuss.elastic.co/u/Gianfranco_Demarco)\ **Post date:** [November 30, 2023, 10:59am UTC](https://discuss.elastic.co/t/kibana-visualization-average-of-sum-of-grouped-values/348326/1 "2023-11-30T10:59:52Z")
Hello, i'm trying to achieve the following result using Kibana visualizations (Lens or others). I have an index where each document is a service delivery to a user. The documents have a cost and a user\_id. I want two visualizations: 1. A chart of average costs by month, and this is easily achieved using Lens. 2. A chart of average costs by user by month. This logically would need to: - group service deliveries by user - sum the costs of the documents grouped together - average the costs - plot these by month Is it possible to achieve 2) with any visualization tool in Kibana? I guess it's probably doable using Vega, but i'd avoid it if possible. Thanks ---
**Author:** ![drewdaemon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/drewdaemon/32/97779_2.png) [@drewdaemon](https://discuss.elastic.co/u/drewdaemon)\ **Post date:** [November 30, 2023, 5:25pm UTC](https://discuss.elastic.co/t/kibana-visualization-average-of-sum-of-grouped-values/348326/2 "2023-11-30T17:25:14Z")
For visualization 2, you should be able to do this with Lens using the collapse by functionality (basically a group-by + a sum). To do this, add a breakdown dimension on `user_id` and configure it like so: ![image](https://us1.discourse-cdn.com/elastic/original/3X/5/3/53662656f39946ab645a709afcbfe7e332b339b3.png) ---
**Author:** ![nextgen](https://avatars.discourse-cdn.com/v4/letter/n/90ced4/32.png) [@nextgen](https://discuss.elastic.co/u/nextgen)\ **Post date:** [December 1, 2023, 3:01am UTC](https://discuss.elastic.co/t/kibana-visualization-average-of-sum-of-grouped-values/348326/3 "2023-12-01T03:01:58Z")
> [@Gianfranco\_Demarco](#): > > i'm trying to achieve the following result using Kibana visualizations (Lens or others). > > I have an index where each document is a service delivery to a user. > The documents have a cost and a user\_id. > > I want two visualizations: > > 1. A chart of average costs by month, and this is easily achieved using Lens. > 2. A chart of average costs by user by month. This logically would need to: > > - group service deliveries by user > - sum the costs of the documents grouped together > - average the costs > - plot these by month > > Is it possible to achieve 2) with any visualization tool in Kibana? > I guess it's probably doable using Vega, but i'd avoid it if possible. > > Thanks Yes, achieving the second visualization (average costs by user by month) in Kibana is possible using the Aggregation features in Lens. You can follow these steps: 1. **Create a New Lens Visualization:** Start by creating a new Lens visualization in Kibana. 2. **Select Date Histogram:** Drag and drop the date field (representing the service delivery date) to the "Date" field well. This will create a date histogram. 3. **Split Chart by User:** Drag and drop the `user_id` field to the "Break down by" field well. This will split the chart by user. 4. **Aggregate Costs:** Drag and drop the `cost` field to the "Values" field well. By default, it might use "Sum" as the aggregation. Change it to "Average" to get the average cost per user per month. 5. **Adjust Labels and Formatting:** You may want to adjust the labels, axis names, and other formatting options to make the visualization more readable. 6. **Save the Visualization:** Once you're satisfied with the configuration, save the visualization. ---
**Author:** ![Gianfranco\_Demarco](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gianfranco_demarco/32/127563_2.png) [@Gianfranco\_Demarco](https://discuss.elastic.co/u/Gianfranco_Demarco)\ **Post date:** [December 1, 2023, 11:25am UTC](https://discuss.elastic.co/t/kibana-visualization-average-of-sum-of-grouped-values/348326/4 "2023-12-01T11:25:59Z")
Thank you both for you kind answers. Unfortunately, i'm still not able to achieve the desired result. Regarding @drewdaemon proposal, i've configured a drilldown on the user\_id. If i set _average of cost_ on Y-axis, without collapse option, I get this: ![image](https://us1.discourse-cdn.com/elastic/original/3X/c/c/ccb80ec91242fa3ce2e924a8199f68744679e2cd.png) Then, if I set _collapse by Sum_, the result is the _sum of averages_, not _averages of sums_ as wanted. (I cannot put another image being a new user; the result is, on the column for 2023-05-23, the number 468, the sum of the average values). Even if I switch the functions sum and average in drilldown and Y-axis, I'm still getting something else. Moreover, the fact that I have to set a number of values feels a little bit weird. What if the maximum number isn't enough? About @nextgen suggestion, following your instructions I end up with each bar split with user\_id reference. I see where you're coming from; my bad for not explaining it clearly enough. What i actually need is to _sum the costs for each user_, so that I get the total cost for a user (composed of different services). Then, I want to average them, to get the _average cost by user_. I'm probably missing something, I feel like this shouldn't be that difficult to do. I'm thinking that maybe I could create another index with this information better organized to make it easier to create the visualization? To be as clear as i can, what I want to achieve is equivalent to this SQL query (which I'm using to check the numerical results of my tests on Kibana): ```auto SELECT AVG(user_cost), YEAR, month FROM ( SELECT SUM(cost) AS user_cost, EXTRACT (YEAR FROM service_timestamp) AS year, EXTRACT (MONTH FROM service_timestamp) AS MONTH FROM service_deliveries WHERE cost IS NOT NULL GROUP BY user_id, YEAR, MONTH ORDER BY YEAR DESC, MONTH DESC ) DATA GROUP BY YEAR, MONTH ORDER BY YEAR DESC, MONTH DESC ``` Thanks a lot for your support! ---
**Author:** ![drewdaemon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/drewdaemon/32/97779_2.png) [@drewdaemon](https://discuss.elastic.co/u/drewdaemon)\ **Post date:** [December 1, 2023, 3:02pm UTC](https://discuss.elastic.co/t/kibana-visualization-average-of-sum-of-grouped-values/348326/5 "2023-12-01T15:02:43Z")
@Gianfranco_Demarco if you want the average of sums instead of the sum of the averages, you can just swap the operations. Specifically - change your metric dimension to use the Lens Sum function - change your breakdown dimension collapse by setting to "average" > [@Gianfranco\_Demarco](#): > > Moreover, the fact that I have to set a number of values feels a little bit weird. What if the maximum number isn't enough? I hear you. To be clear, Lens uses the [terms aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html) under the hood. Elasticsearch is a distributed system that is built to deal with vast amounts of data. Specifying a number of values helps bound the amount of work that is being requested. Without controls like this, you could inadvertently set off a massive amount of work on high-cardinality data sets. I think that's the reason it works this way. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 29, 2023, 3:03pm UTC](https://discuss.elastic.co/t/kibana-visualization-average-of-sum-of-grouped-values/348326/6 "2023-12-29T15:03:27Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.