Data Table: calculate average from output of two sum aggregations

Elastic Stack Kibana
1

Here's an example:

image
image.png1491×537 57 KB

Currently the Gross Margin uses a script, and the problem is it calculates a percentage for every document. Averaging that percentage does not give the "overall average" because it's not weighted. e.g. a $100 deal with 95% Gross Margin is treated the same as a $1,000,000 deal with 5% Gross Margin. You can see this if you manually divide GM/Revenue in the results, the percentage doesn't match.

The obvious solution is to calculate the Gross Margin percentage based on the output of the two previous sum aggregations. The problem is I can't find any way to do that.

I'm using Elastic cloud and have the latest Kibana and ES software. I've been looking into pipeline aggregations, but seems like they aren't quite what I need.

I know this kind of math can be done in timelion graphs, but I need it in this data table.

Are there any solutions (I'm open to workarounds/hacks) to make this work?

2

Elastic cloud doesnt support plugins, but you could do what you need in your own local E/K using this plugin : https://github.com/datasweet-fr/kibana-datasweet-formula/

regards

Lionel

3

Ahh, very nice. Is elastic cloud supporting plugins a feature on the roadmap or intentionally disabled?

4

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