# Sum of Average aggregation in visualization

**URL:** <https://discuss.elastic.co/t/sum-of-average-aggregation-in-visualization/238533>\
**Category:** Kibana\
**Created:** [June 24, 2020, 4:58pm UTC](https://discuss.elastic.co/t/sum-of-average-aggregation-in-visualization/238533 "2020-06-24T16:58:08Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![umay\_fb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/umay_fb/32/60582_2.png) [@umay\_fb](https://discuss.elastic.co/u/umay_fb)\
**Post date:** [June 24, 2020, 4:58pm UTC](https://discuss.elastic.co/t/sum-of-average-aggregation-in-visualization/238533/1 "2020-06-24T16:58:09Z")

</div>

i using this script to get sum of average aggregation

Script:

```
{
  "size": 0,
  "aggs": {
    "by category": {
      "terms": {
        "field": "spv.keyword"
      },
      "aggs": {
        "by user_id": {
          "terms": {
            "field": "sales.keyword"
          },
          "aggs": {
            "avg by user": {
              "avg": {
                "field": "target"
              }
            }
          }
        },
        "average by user, category": {
          "sum_bucket": {
            "buckets_path": "by user_id>avg by user"
          }
        }
      }
    }
  }
}

```

the value i get is

Value:

```
  "aggregations" : {
    "by category" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [{"key" : "SPV1",
          "doc_count" : 13265,
          "by user_id" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "SALES1",
                "doc_count" : 5184,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES2",
                "doc_count" : 2407,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES3",
                "doc_count" : 2244,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES4",
                "doc_count" : 1977,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES5",
                "doc_count" : 1446,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES6",
                "doc_count" : 7,
                "avg by user" : {
                  "value" : null
                }
              }
            ]
          },
          "average by user, category" : {
            "value" : 150.0
          }
        },
        {
          "key" : "SPV2",
          "doc_count" : 9416,
          "by user_id" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "SALES7",
                "doc_count" : 3955,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES8",
                "doc_count" : 2655,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES9",
                "doc_count" : 1701,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES10",
                "doc_count" : 1105,
                "avg by user" : {
                  "value" : 30.0
                }
              }
            ]
          },
          "average by user, category" : {
            "value" : 120.0
          }
        }

```

how can i create a visualization in kibana which has value average by user,category like :

SPV Value  
SPV1 150  
SPV2 120

Thanks...

---

<div class="post-metadata">

**Author:** ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)\
**Post date:** [July 15, 2020, 10:37am UTC](https://discuss.elastic.co/t/sum-of-average-aggregation-in-visualization/238533/2 "2020-07-15T10:37:48Z")

</div>

You can do that with the `Table` widget from `Visualize` app.

Using the **Kibana Sample Flight Data** , I got these two tables. On the left you got the different **destination countries** , along with the sum of the average **Distance in Miles** by **Carrier**.. On the left, to check it's correct, the table shows the average distance by carrier, filtered for the first country in the left.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/6/d61cd8f30ffa4022eb94202eb26cfba088496d10.png)

And here you have how the table on the left was configured.

### Buckets

A simple term aggregation by destination country to split the table rows

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/1/d1c688e0a4444bc6ddac2aeb31f811d66c4cb1ac.png)

### Metrics

And for the metric part you need to first select a `Sum Bucket` where the buckets are defined as a `Terms` aggregation by **Carrier** and for each bucket you'll compute the `Average` of the **distanceMiles** field.

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/4/2407956b477860fc44b1109b4dbdae9c62aea217.png)

I hope this is clear enough, let us know what you think.

---

<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:** [August 12, 2020, 10:44am UTC](https://discuss.elastic.co/t/sum-of-average-aggregation-in-visualization/238533/3 "2020-08-12T10:44:12Z")

</div>

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