# How to calculate the average count with term **URL:** \ **Category:** Kibana\ **Created:** [September 22, 2021, 2:03pm UTC](https://discuss.elastic.co/t/how-to-calculate-the-average-count-with-term/284879 "2021-09-22T14:03:55Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![forst](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forst/32/94565_2.png) [@forst](https://discuss.elastic.co/u/forst)\ **Post date:** [September 22, 2021, 2:03pm UTC](https://discuss.elastic.co/t/how-to-calculate-the-average-count-with-term/284879/1 "2021-09-22T14:03:55Z")
I am looking to make a heatmap type visualization, I wanted to put the time on x-axis and the team (name) on y-axis. a team has a number of different jobs I wanted to put the average count with : count total / count job but when I select average in the metrics parameter, the field does not have the field team, nor profession. What should I do? thanks for your help ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [September 23, 2021, 9:21am UTC](https://discuss.elastic.co/t/how-to-calculate-the-average-count-with-term/284879/2 "2021-09-23T09:21:03Z")
Hi @forst , can you post an example document to better understand the fields data? Also, which version of the stack are you using? ---
**Author:** ![forst](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forst/32/94565_2.png) [@forst](https://discuss.elastic.co/u/forst)\ **Post date:** [September 23, 2021, 11:29am UTC](https://discuss.elastic.co/t/how-to-calculate-the-average-count-with-term/284879/3 "2021-09-23T11:29:07Z")
the stack's version is 7.13.4 here the data: ```auto > "mappings" : { > "properties" : { > "@timestamp" : { > "type" : "date" > }, > "team" : { > "type" : "text", > "fields" : { > "keyword" : { > "type" : "keyword", > "ignore_above" : 256 > } > } > }, > "job" : { > "type" : "text", > "fields" : { > "keyword" : { > "type" : "keyword", > "ignore_above" : 256 > } > } > }, > "externe" : { > "type" : "boolean", > "doc_values" : false > } > } ``` i would have the result : average of a team that works as a dev : 58% work as dev 60% works in externally ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [September 23, 2021, 4:19pm UTC](https://discuss.elastic.co/t/how-to-calculate-the-average-count-with-term/284879/4 "2021-09-23T16:19:40Z")
If you want to show more than one metric per team, per Heatmap cell, you probably need to create two distinct visualizations: for instance, one per internal and the other one per external. In your current stack version you have to probably use something like Vega to build a custom heatmap visualization with all the calculation you're looking for. From Kibana 7.15 you have few more options with the Lens editor, which includes a Heatmap chart type, the filtered metric type and Formulas. Having the newer Lens version you can do: - Pick the Heatmap visualization (still experimental) - on the Horizontal Axis you can put a date histogram, which means drop the "@timestamp" field - on the Vertical Axis drop the "team.keyword" field, or configure a "Top Values" dimension for the field "team.keyword" - On the Cell value you could select the Formula tab and type `count(kql="external: false") / count()` to check how much work is spent internally Here's an example using the sample data logs dataset, where I've used a filtered formula: ![Screenshot 2021-09-23 at 18.17.13](https://us1.discourse-cdn.com/elastic/original/3X/b/d/bdc1e0f83b6d52c926728b701cd102b890c600b6.png) Does it look like what you are looking for? ---
**Author:** ![forst](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forst/32/94565_2.png) [@forst](https://discuss.elastic.co/u/forst)\ **Post date:** [September 27, 2021, 8:13am UTC](https://discuss.elastic.co/t/how-to-calculate-the-average-count-with-term/284879/5 "2021-09-27T08:13:50Z")
Hi @Marco_Liberati thanks for your answers. I unfortunately cannot update to kibana 7.15. I don't know Vega. Are there any examples of how to use Vega with the mean computation with heatmap other than the one in the documentation? ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [September 27, 2021, 12:31pm UTC](https://discuss.elastic.co/t/how-to-calculate-the-average-count-with-term/284879/6 "2021-09-27T12:31:26Z")
If you cannot upgrade the only option is Vega then. In the official documentation you can find many examples on how to use it: [Example Gallery | Vega-Lite](https://vega.github.io/vega-lite/examples/) ---
**Author:** ![forst](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/forst/32/94565_2.png) [@forst](https://discuss.elastic.co/u/forst)\ **Post date:** [September 27, 2021, 1:38pm UTC](https://discuss.elastic.co/t/how-to-calculate-the-average-count-with-term/284879/7 "2021-09-27T13:38:40Z")
@Marco_Liberati I saw the documentation but I do not understand how to put the name of a team in the y axis ---
**Author:** ![Marco\_Liberati](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marco_liberati/32/82953_2.png) [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)\ **Post date:** [September 27, 2021, 2:31pm UTC](https://discuss.elastic.co/t/how-to-calculate-the-average-count-with-term/284879/8 "2021-09-27T14:31:44Z")
While this blog post is not official from Elastic, you may find it useful to understand how to build a Vega heatmap step by step in Kibana: [Awesome Visualizations with Kibana & Vega](https://spinscale.de/posts/2021-06-23-using-kibana-vega-for-awesome-visualizations.html) ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 25, 2021, 2:32pm UTC](https://discuss.elastic.co/t/how-to-calculate-the-average-count-with-term/284879/9 "2021-10-25T14:32:12Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.