# Weighted Average in a DataTable of Kibana

I have this index:

``````PUT sales
{
"settings": {
"index": {
"number_of_shards": 3,
"number_of_replicas": 0
}
},
"mappings": {
"properties": {
"@timestamp": {
"type": "date"
},
"@version": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"product": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"volume": {
"type": "float"
},
"cost": {
"type": "float"
}
}
}
}

POST sales/_doc
{
"product": "Product 1",
"volume": 4,
"cost": 3
}

POST sales/_doc
{
"product": "Product 1",
"volume": 10,
"cost": 3
}
``````

I want to calculated the cost average based on volume.

If I have this 2 docs, I have to calculate like this:
=(sum(cost * volume))/sum(volume)
=(3 * 4 + 3 * 10) / 10 +4

How can I do this on a kibana Data Table?

I want the table like this:

Product_____| Average
Product 1 | 8.2

Hi Henrique, I think solving your problem will be a two-step process.

1. You'll need to create a per-document field for `cost * volume`, e.g. a field called `volumeCost`. You could create this field two ways, either by reindexing your data and adding the field with a Painless script, or by creating a scripted field that calculates this value on the fly. The first option is a little more performant when querying your data but the second option might be easier.

2. I think you can create a Timeseries visualization in the Visualize app to generate the table you want. In the screenshot below, imagine the first aggregation for `products.base_price` is actually a reference to the `volumeCost` field and the second aggregation for `products.quantity` is actually a reference to the `volume` field. This way you can define a bucket script that performs the calculation you want, and order the results by product ID or name in a table.

Also, if you're interested in tracking progress for supporting bucket scripts in our other visualization types, please follow this issue: https://github.com/elastic/kibana/issues/4707.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.