# Adding a new columns to a data table from a calculation based on two different columns in the same row

**URL:** <https://discuss.elastic.co/t/adding-a-new-columns-to-a-data-table-from-a-calculation-based-on-two-different-columns-in-the-same-row/242952>\
**Category:** Kibana\
**Created:** [July 28, 2020, 5:24pm UTC](https://discuss.elastic.co/t/adding-a-new-columns-to-a-data-table-from-a-calculation-based-on-two-different-columns-in-the-same-row/242952 "2020-07-28T17:24:29Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![MyQuery](https://avatars.discourse-cdn.com/v4/letter/m/839c29/32.png) [@MyQuery](https://discuss.elastic.co/u/MyQuery)\
**Post date:** [July 28, 2020, 5:24pm UTC](https://discuss.elastic.co/t/adding-a-new-columns-to-a-data-table-from-a-calculation-based-on-two-different-columns-in-the-same-row/242952/1 "2020-07-28T17:24:29Z")

</div>

Hi,  
The user wants to see if there is a way to add a two additional column (Calculated columns) to data table like below table.

Market | Total | D1 | D2 | Total D | Percentage D1 | Percentage D2 | percentage Total D  
Source 10 3 3 6 **?? ?? ??**  
Target 15 4 9 13 **?? ?? ??**

 ![snipper1](https://us1.discourse-cdn.com/elastic/original/3X/9/a/9aa6f5757ddaf30d203b1d5e8d9c5681574defc4.png)  
Total, D1 and D2 are coming as a count from aggregation columns from index .  
Not sure how to generate percentage of D1,D2 and total D .

Can someone please help here

---

<div class="post-metadata">

**Author:** ![BenB196](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/benb196/32/83401_2.png) [@BenB196](https://discuss.elastic.co/u/BenB196)\
**Post date:** [July 29, 2020, 1:46am UTC](https://discuss.elastic.co/t/adding-a-new-columns-to-a-data-table-from-a-calculation-based-on-two-different-columns-in-the-same-row/242952/2 "2020-07-29T01:46:23Z")

</div>

You can do this with scripted fields. You'll need to setup the fields ahead of time and then reference them in the visualization. [https://www.elastic.co/guide/en/kibana/current/scripted-fields.html](https://www.elastic.co/guide/en/kibana/current/scripted-fields.html). There is no way to do this on the fly in a visualization itself. (Depending on the amount of data you're talking about it may be better to generate the fields on ingest rather than scripting on the fly)

---

<div class="post-metadata">

**Author:** ![MyQuery](https://avatars.discourse-cdn.com/v4/letter/m/839c29/32.png) [@MyQuery](https://discuss.elastic.co/u/MyQuery)\
**Post date:** [July 29, 2020, 5:00pm UTC](https://discuss.elastic.co/t/adding-a-new-columns-to-a-data-table-from-a-calculation-based-on-two-different-columns-in-the-same-row/242952/3 "2020-07-29T17:00:53Z")

</div>

Thank you Ben for the details. I am trying for percentile calculation .  
For this, I have looped through the documents and taken total records . Another loop for conditional value from the total like success. Post to the values when i am trying to do percentile like below it is not working  
percentile = ((100\*success) / count);  
Multiplication is working fine but division is not working. Any thoughts on this issue? Do we have any percentile operator for doing percentage calculation?

---

<div class="post-metadata">

**Author:** ![BenB196](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/benb196/32/83401_2.png) [@BenB196](https://discuss.elastic.co/u/BenB196)\
**Post date:** [July 29, 2020, 5:14pm UTC](https://discuss.elastic.co/t/adding-a-new-columns-to-a-data-table-from-a-calculation-based-on-two-different-columns-in-the-same-row/242952/4 "2020-07-29T17:14:42Z")

</div>

There is no percentage operator from the looks of it, [https://www.elastic.co/guide/en/elasticsearch/painless/current/painless-operators.html](https://www.elastic.co/guide/en/elasticsearch/painless/current/painless-operators.html). I can't really help with the formula you provided as it doesn't seem to be complete and in the painless syntax.

---

<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 26, 2020, 5:14pm UTC](https://discuss.elastic.co/t/adding-a-new-columns-to-a-data-table-from-a-calculation-based-on-two-different-columns-in-the-same-row/242952/5 "2020-08-26T17:14:44Z")

</div>

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