Splunk Search

## Need to calculate weighted average across columns like addtotals does sum across columns (from chart command)

Path Finder

I have asked almost the same question here. I will try to explain my question better here

My command looks like this

``````... | stats sum(field1) as sumf1, sum(field2) as countf2 by  field3,field4  | eval avgf5 = (sumf1/countf2 )  | chart sum(avgf5) as avgf5 over field4 by  field3
``````

The chart command transposes field3 values across columns... This produces a tables with columns(that is equal to no of distinct field3 values) ....

If I want to have a sum across cols I can use addtotals What can I use for average(weighted) across cols? Can I write my own function?

Tags (3)
1 Solution
Splunk Employee

You should be able to make `field3` a multivalued field that contains its original value as well as the value `ALL` so that the `stats` command calculates the global sum and count for every value of `field4`, stored as `field3=ALL` and then aggregated properly later. Let's assume that `field3` never has commas in it:

``````... | eval field3 = field3 . ",ALL"
| makemv delim="," field3
| stats sum(field1) as sumf1, count(field2) as countf2 by field3 field4
| eval avgf5 = (sumf1/countf2)
| chart first(avgf5) over field4 by field3
``````

If field3 does have commas in it, you can just pick another delimiter.

Splunk Employee

You should be able to make `field3` a multivalued field that contains its original value as well as the value `ALL` so that the `stats` command calculates the global sum and count for every value of `field4`, stored as `field3=ALL` and then aggregated properly later. Let's assume that `field3` never has commas in it:

``````... | eval field3 = field3 . ",ALL"
| makemv delim="," field3
| stats sum(field1) as sumf1, count(field2) as countf2 by field3 field4
| eval avgf5 = (sumf1/countf2)
| chart first(avgf5) over field4 by field3
``````

If field3 does have commas in it, you can just pick another delimiter.

Path Finder

This solution is simply awesome....

Path Finder

Or When we use an avg function in chart command, is there a way to get overall average ?

Get Updates on the Splunk Community!

#### Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

#### Take the 2021 Splunk Career Survey for \$50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...

#### Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...