# Weighted Average in a DataTable of Kibana

**URL:** <https://discuss.elastic.co/t/weighted-average-in-a-datatable-of-kibana/185909>\
**Category:** Kibana\
**Created:** [June 14, 2019, 8:47pm UTC](https://discuss.elastic.co/t/weighted-average-in-a-datatable-of-kibana/185909 "2019-06-14T20:47:41Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Henrique\_Santiago](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/henrique_santiago/32/47553_2.png) [@Henrique\_Santiago](https://discuss.elastic.co/u/Henrique_Santiago)\
**Post date:** [June 14, 2019, 8:47pm UTC](https://discuss.elastic.co/t/weighted-average-in-a-datatable-of-kibana/185909/1 "2019-06-14T20:47:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![cjcenizal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cjcenizal/32/11216_2.png) [@cjcenizal](https://discuss.elastic.co/u/cjcenizal)\
**Post date:** [June 18, 2019, 7:16pm UTC](https://discuss.elastic.co/t/weighted-average-in-a-datatable-of-kibana/185909/2 "2019-06-18T19:16:54Z")

</div>

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](https://www.elastic.co/guide/en/elasticsearch/reference/6.0/docs-reindex.html#docs-reindex) and adding the field with a Painless script, or by creating a [scripted field](https://www.elastic.co/guide/en/kibana/current/scripted-fields.html) 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.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/8/4/845b6886d3f4966095da3ddf94e75688c16f8bca.png)

---

<div class="post-metadata">

**Author:** ![cjcenizal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cjcenizal/32/11216_2.png) [@cjcenizal](https://discuss.elastic.co/u/cjcenizal)\
**Post date:** [June 18, 2019, 7:18pm UTC](https://discuss.elastic.co/t/weighted-average-in-a-datatable-of-kibana/185909/3 "2019-06-18T19:18:20Z")

</div>

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](https://github.com/elastic/kibana/issues/4707).

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 16, 2019, 7:31pm UTC](https://discuss.elastic.co/t/weighted-average-in-a-datatable-of-kibana/185909/4 "2019-07-16T19:31:04Z")

</div>

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