# How do I make a Kibana histogram that plots the distribution of the average of key A for each distinct value of key B? **URL:** \ **Category:** Kibana\ **Created:** [January 28, 2021, 6:00pm UTC](https://discuss.elastic.co/t/how-do-i-make-a-kibana-histogram-that-plots-the-distribution-of-the-average-of-key-a-for-each-distinct-value-of-key-b/262541 "2021-01-28T18:00:38Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![eric4](https://avatars.discourse-cdn.com/v4/letter/e/bbe5ce/32.png) [@eric4](https://discuss.elastic.co/u/eric4)\ **Post date:** [January 28, 2021, 6:00pm UTC](https://discuss.elastic.co/t/how-do-i-make-a-kibana-histogram-that-plots-the-distribution-of-the-average-of-key-a-for-each-distinct-value-of-key-b/262541/1 "2021-01-28T18:00:38Z")
This applies to any Kibana graph but the one I am interested in is a Histogram. My data is structured like this: ``` [ { "user_id" : 3, "score" : 10 }, { "user_id" : 1, "score" : 20 }, { "user_id" : 2, "score" : 60 }, { "user_id" : 1, "score" : 10 }, { "user_id" : 2, "score" : 55 } ] ``` How do I make a Kibana histogram that plots the distribution of the average `score` for each distinct `user_id` ? The graph that I'm looking for looks like this: ![Screen Shot 2021-01-28 at 12.05.54 PM](https://us1.discourse-cdn.com/elastic/original/3X/c/a/ca0b42df2e22171ecb475ca40d110e9793c22b42.png) I was able to make this graph using the "Unique Count" aggregation on `user_id` , but it is incorrect because it does not average the values. This question is identical to [this one](https://stackoverflow.com/questions/35480358/elasticsearch-calculate-average-of-unique-values) except I am interested in graphing the distribution of average "price" for each "color" ---
**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\ **Post date:** [January 28, 2021, 7:53pm UTC](https://discuss.elastic.co/t/how-do-i-make-a-kibana-histogram-that-plots-the-distribution-of-the-average-of-key-a-for-each-distinct-value-of-key-b/262541/2 "2021-01-28T19:53:04Z")
Hi, welcome to the forums! Based on your sample chart, you should use the `histogram` aggregation on the X axis, on the `score` field, and the `cardinality/unique count` metric on the Y axis, on the `user_id` field. Your histogram interval determines how many X axis values you will see, like in the sample chart you have it set to 2. To do this in Lens, you would use the function called `Ranges`- this function sets an interval automatically, but you can override the interval if you want. On the vertical axis you would choose `Unique count` To do this in the Bar Chart, you would choose Histogram for your X axis, then Cardinality for your Y axis. ---
**Author:** ![eric4](https://avatars.discourse-cdn.com/v4/letter/e/bbe5ce/32.png) [@eric4](https://discuss.elastic.co/u/eric4)\ **Post date:** [January 28, 2021, 8:28pm UTC](https://discuss.elastic.co/t/how-do-i-make-a-kibana-histogram-that-plots-the-distribution-of-the-average-of-key-a-for-each-distinct-value-of-key-b/262541/3 "2021-01-28T20:28:21Z")
Hi, Thanks for the reply, I have tried this but this skips the average step altogether. I want to bucket the documents based on distinct `user_id` and then average each bucket's `score` value. I then want to show the count distribution of the scores. Thanks, Eric ---
**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\ **Post date:** [January 28, 2021, 8:54pm UTC](https://discuss.elastic.co/t/how-do-i-make-a-kibana-histogram-that-plots-the-distribution-of-the-average-of-key-a-for-each-distinct-value-of-key-b/262541/4 "2021-01-28T20:54:06Z")
It depends on how many unique user IDs you have. Is it less than 10k? If so, then you can probably do that in a single request. Is it more than 10k? Then you'll need to pre-compute the numbers you want. Pre-computing also works for smaller datasets. a. One way to precompute is using [Elasticsearch transforms](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html). This will create a pivoted index which lets you transform the documents you shared into documents like `{ count: 100, score: 10.0 }` using a `Terms` aggregation on score, with `count` using the Terms.count. This lets you create a histogram on the `score` field where you can show `sum of count`. You can't put the histogram inside the ES Transform because the data format won't work in Kibana. b. You can build a Vega chart that does this transformation for you. You will need to query all the aggregated data, using a Terms aggregation on user\_id, and then an Average of Score as your metric. Set the size of the terms aggregation to 65000, which is the max buckets you can fetch. In Vega, you can then apply a second level of aggregation to make a histogram chart. Follow the [Vega docs](https://www.elastic.co/guide/en/kibana/master/vega.html) to get started with this. ---
**Author:** ![eric4](https://avatars.discourse-cdn.com/v4/letter/e/bbe5ce/32.png) [@eric4](https://discuss.elastic.co/u/eric4)\ **Post date:** [January 28, 2021, 9:17pm UTC](https://discuss.elastic.co/t/how-do-i-make-a-kibana-histogram-that-plots-the-distribution-of-the-average-of-key-a-for-each-distinct-value-of-key-b/262541/5 "2021-01-28T21:17:41Z")
Okay thanks for explaining that. Is there any way I can use visual builder to do something like this? The general question I am trying to answer is, **how many** users are scoring well over time? Several scores for the same user can come in at the same time, which is why I want to average them. In visual builder, can I plot a single line that shows the count of users with average scores above a certain threshold? ---
**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\ **Post date:** [January 28, 2021, 9:36pm UTC](https://discuss.elastic.co/t/how-do-i-make-a-kibana-histogram-that-plots-the-distribution-of-the-average-of-key-a-for-each-distinct-value-of-key-b/262541/6 "2021-01-28T21:36:32Z")
If you really want to calculate that kind of chart without changing your data format, you need to use Vega. Nothing else is as powerful in Kibana. You can't do this in TSVB _with your data format_ because there are too many steps involved in the presentation of the data. You _can_ do this in TSVB if you first transform the data to have a per-user average. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 25, 2021, 9:36pm UTC](https://discuss.elastic.co/t/how-do-i-make-a-kibana-histogram-that-plots-the-distribution-of-the-average-of-key-a-for-each-distinct-value-of-key-b/262541/7 "2021-02-25T21:36:33Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.