Table visualization: calculate percentage

[simonlucalandi]

Hi all.
Let's say that I have the following documents in an index

{
	"user": "foo",
    "cost": 10,
    "revenue": 12
},
{
	"user": "bar",
    "cost": 20,
    "revenue": 25
}
{
	"user": "foo",
    "cost": 15,
    "revenue": 20
},
{
	"user": "bar",
    "cost": 15,
    "revenue": 30
}

I need to create a Table visualization to show the margin and the margin_percent per user, where margin is sum of revenue - sum of cost and margin_percent is margin / sum of revenue.

For this example I should have:

user	cost	revenue	margin	margin_percent
foo	25	32	7	21.87
bar	35	55	20	36.36

I can easily add a calculated field in the index, margin = doc[revenue].value - doc[cost].value and then aggregate using a sum function, but how to calculate the margin_percent?

Any suggestion is appreciated.

[preetish_P (Preetish Pai)]

Hi @simonlucalandi ,

Create an enhanced table with 2 metrics: Sum of cost and Sum of revenue, then in the options tab create two computed columns to calculate : (sum of cost) - (sum of revenue) and (new column)%(Sum of cost)

[stephenb (Stephen Brown)]

Perhaps use Lens ... this is very simple using Formulas

[simonlucalandi]

thank you! I'll give it a try!