# Calculating count per term filter within one metric
**URL:** https://discuss.elastic.co/t/calculating-count-per-term-filter-within-one-metric/189088
**Category:** Kibana
**Created:** [July 5, 2019, 11:44am UTC](https://discuss.elastic.co/t/calculating-count-per-term-filter-within-one-metric/189088 "2019-07-05T11:44:45Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@saif3r](https://discuss.elastic.co/u/saif3r)
#### Post date: [July 5, 2019, 11:44am UTC](https://discuss.elastic.co/t/calculating-count-per-term-filter-within-one-metric/189088/1 "2019-07-05T11:44:45Z")
Hello All,
I am currently working with a simple data set where each row represents an entry with one of three possible states. Let's say i have 5 documents in my index:
```
{
"@timestamp": "2019-07-01T11:39:00.132Z",
"name": "Car1",
"state": "NA"
}
{
"@timestamp": "2019-07-02T11:39:00.132Z",
"name": "Car2",
"state": "OK"
}
{
"@timestamp": "2019-07-03T11:39:00.132Z",
"name": "Car3",
"state": "NOT OK"
}
{
"@timestamp": "2019-07-04T11:39:00.132Z",
"name": "Car4",
"state": "OK"
}
{
"@timestamp": "2019-07-05T11:39:00.132Z",
"name": "Car5",
"state": "OK"
}
```
In above example, we have 5 documents:
1 with state: NA
1 with state: NOT OK
3 with state: OK
I would like to have a metric that would count in percentage the ratio of (OK) to (NOT OK + OK), so based on above documents, we have a calculation: **3/4** which should show us a metric of **75%**. It's important to have an option to color the background based on the predefined threshold (eg 0% - 90% red, 91% - 100% green).
Is that possible? If so, how could I do that? I tried visual builder but applied filter per status applies to entire visualization so I'm not able to effectively 'extract' count of records per each state.
---
### Author:  [@lukeelmers](https://discuss.elastic.co/u/lukeelmers)
#### Post date: [July 5, 2019, 11:43pm UTC](https://discuss.elastic.co/t/calculating-count-per-term-filter-within-one-metric/189088/2 "2019-07-05T23:43:32Z")
I'm not sure if I can think of a way to accomplish this in visualize metrics or visual builder, though you can perform math operations like this in Timelion.
If you used the metric element in Canvas, you could send a timelion function to it and then conditionally style based on the result. An expression along these lines might come close to what you are looking for:
```auto
filters
| timelion
query=".es(index=my_index_name, q=state:OK).divide(.es(index=my_index_name, q=\"state:OK OR state:\"NOT OK\"\"))"
| filterrows {getCell "value" | any {neq null}}
| math "round(multiply(mean(value), 100), 0)"
| metric "% OK"
metricFont={ color={ if {gte 91} then="green" else="red" } }
| render
```
This expression does the following:
1. Queries your index and produces a `value` that is the total number of `OK` entries divided by the total `OK + NOT OK` entries.
2. Filters the resulting value to remove any `null` entries (may or may not be necessary depending on your data set)
3. Takes the avg of the selected values (based on your time range), and converts them to a percentage.
4. Creates a metric with that value which is conditionally red/green based on whether the value is below 91.
There are probably some other ways to accomplish this, but wanted to share the first idea that came to mind in hopes that it points you in the right direction!
---
### Author:  [@saif3r](https://discuss.elastic.co/u/saif3r)
#### Post date: [July 8, 2019, 7:04am UTC](https://discuss.elastic.co/t/calculating-count-per-term-filter-within-one-metric/189088/3 "2019-07-08T07:04:02Z")
Hello @lukeelmers,
Thank you for the answer. This Timelion query seems to be working just fine. I've learned a lot, since i have't used it until now, so thank you very much for that.
As for the result itself, I'm looking for slightly different type of visual presentation, and I apologize for not mentioning it earlier. I have a dashboard with around 5 different visualizations. My intention is to put this calculation among other within same dashboard, but as a metric based on a set of filters i have. So in this case, I can't use Canvas as some of those visualizations cannot be placed there (at least not with my level of knowledge). Also instead of line chart Timelion shows, I need to have a single field with a number. More or less similar to what we can see in _[Metricbeat System] Overview ECS_ Dashboard provided by Kibana.
Is that something that could be achieved with current data model?
Thanks in advance.
---
### Author:  [@lukeelmers](https://discuss.elastic.co/u/lukeelmers)
#### Post date: [July 9, 2019, 9:21pm UTC](https://discuss.elastic.co/t/calculating-count-per-term-filter-within-one-metric/189088/4 "2019-07-09T21:21:39Z")
Ah, yeah if you need to make calculations like this which are based on other computed metrics/buckets, then what you are probably looking for is a [bucket script aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-script-aggregation.html) or a [scripted metric aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-scripted-metric-aggregation.html). Unfortunately these aren't currently supported in Kibana, but there are Github issues tracking support [here](https://github.com/elastic/kibana/issues/4707) and [here](https://github.com/elastic/kibana/issues/2646).
In the meantime, Timelion with Canvas is probably the best workaround I can think of for achieving a similar result
---
### Author:  [@saif3r](https://discuss.elastic.co/u/saif3r)
#### Post date: [July 15, 2019, 6:35am UTC](https://discuss.elastic.co/t/calculating-count-per-term-filter-within-one-metric/189088/5 "2019-07-15T06:35:11Z")
Understood, thank you @lukeelmers
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [August 12, 2019, 6:35am UTC](https://discuss.elastic.co/t/calculating-count-per-term-filter-within-one-metric/189088/6 "2019-08-12T06:35:24Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.