I'm currently implementing Kibana 4 (v4.0.0) as a financial dashboard for our company, order data is originating from ElasticSearch. I'm struggling with the absence of a weighted average metric aggregation for calculation of the gross profit margin for a certain time interval. A possibility exists to write a custom metric, however Kibana does not show/implement custom metrics in the table visualization construction.
A small visualization of sample data:
**Order 1**
Revenue: 1000
Cost of goods sold: 800
Gross margin: 200
Gross margin percentage: 200 / 1000 = 0.2 (= 20%)
**Order 2**
Revenue: 100
Cost of goods sold: 20
Gross margin: 80
Gross margin percentage: 80 / 100 = 0.8 (= 80%)
Please refer to this page for calculation of the gross margin percentage
I set the gross margin as a field per elasticsearch document (= order object). For example, order 1 has a 'gross_margin' field equal to 0.2. Simply returning an average of the 'gross_margin' fields (average aggregation) is not sufficient, since this would return 0.5 ((0.2 + 0.8) / 2), but the actual gross margin of the total collection is 0.254 (((1000 + 100) - (800 + 20)) / (1000 + 100)).
Perhaps this weighted average is possible with Kibana via a workaround, but I'm not able to find it in the documentation whatsoever. I would greatly appreciate it if someone could provide guidance. Please don't hesitate to request more information when my question is not clear to you.
