How to calculate the average in the total row?

[boxmetal]

Hi Splunk community,

I need to display data shown as table below

Component	Total units	Violated units	Matched [%]
Type A	1	1	99
Type B	10	10	75
Type C	100	85	85
Total	111	96	86

In the total row, the matched value is the average of the column, while others are the sum value.

Is it possible to insert the average value to the total row as shown?

Here's my SPL:

index="my_index"source="*sourcename*"
| stats count as total_units count(eval(isnull(approval_message))) as violated_units values(matched_percentage) as matched by component 
| addcoltotals total_units + violated_units labelfield=component
| rename total_units as "Total Units", violated_units as "Violated Units", matched as "Matched [%]"

[boxmetal]

Hi @Manasa_401 ,

The table showed average calculation. Thank you a lot!

[Manasa_401 · Accepted solution]

Hello @boxmetal 

Try the below query:

index="my_index"source="*sourcename*"
| stats count as total_units count(eval(isnull(approval_message))) as violated_units values(matched_percentage) as matched by component 
| addcoltotals total_units + violated_units labelfield=component
| streamstats avg(matched) as average
| eval matched=coalesce(matched,average)
| fields - average
| rename total_units as "Total Units", violated_units as "Violated Units", matched as "Matched [%]"

If this helps an upvote would be appreciated.

Thanks,

Manasa