Calculate percentage of each row with respect to the value of a particular row

[Hung_Nguyen]

I have a current output in the form of a table with rows representing the time spent in various checkpoints and the last row being the total time.  I would like to calculate the percentage of each row in relation to the total row "Total Duration" row.  if this is not possible, I am ok with calculated the percentage based on the sum of all the p50/p90 column values as well. 

Marker                           P50         P90
-------------------------------------------
Point 1 Duration         10            20
Point 2 Duration         40            100
Point 3 Duration         50            80
Total Duration             100         200

and I would like to insert a column for the percentage like this (the 100% in the bottom row is optional)
Marker                           P50         P50%             P90            P90%
--------------------------------------------------------------------------------------
Point 1 Duration         10             10%              20                10%
Point 2 Duration         40            40%               100             50%
Point 3 Duration         50            50%               80                40%
Total Duration             100         100%            200             100%

Thank you very much

[PickleRick · Accepted solution]

Question is - where does this "total" row value (or values) comes from. If you generate it with addtotals, just do eventstats sum or any other stat function _before_ that addtotals.

But if you have this row generated by another means and you really want to use this value, there is a trick you can use

<your_search> | eval totalp50=if(Marker="Total Duration",p50,null())
| eval totalp90=if(Marker="Total Duration",p90,null())
| eventstats sum(totalp50) as totalp50 sum(totalp90) as totalp90

 You can use values(), min(), max() or avg() instead of sum() in the eventstats because you only have one value to count the stats from.

[ro_mc]

You can calculate based on the total using eventstats. E.g.

| eventstats sum(P50) as P50sum sum(P90) as P90sum

New columns of P50sum and P90sum will be added to each row.

These will contain the respective values of 100 and 200 that you currently have in the totals row.

Marker P50 P50sum P90 P90sum

Point 1 Duration 10 100 20 200
Point 2 Duration 40 100 100 200
Point 3 Duration 50 100 80 200

From here, you can run eval and fieldformat commands to calculate based on the two row fields:

| eval P50dec = P50/P50sum
| eval P90dec = P90/P90sum
| fieldformat P50pc = printf("%%.1f", P50dec*100)
| fieldformat P90pc = printf("%%.1f", P90dec*100)

The eval commands create exact decimal values, while fieldformat formats these as percentage values to two decimal places without changing the underlying data. If you want, you can use the same field names for both. Finally, table the output to display the fields you want in the desired order.

Marker P50 P50sum P50pc P90 P90sum P90pc
--------------------------------------------------------------------------------------
Point 1 Duration 10 100 10.0% 20 200 10.0%
Point 2 Duration 40 100 40.0% 100 200 50.0%
Point 3 Duration 50 100 50.0% 80 200 40.0%

If you want running totals for each checkpoint, you could use streamstats rather than eventstats instead.

[Hung_Nguyen]

Thank you!