Calculating weighted average in Grafana

Grafana Dashboards
1

Hi all,

Is it possible to calculate weighted averages in Grafana?

Basically I have a “grades” datasource in Elasticsearch, where each student has 4 grades per subject, and each subject has a weight.

I need to calculate the weighted average for each student.

I think I need to use Transformations, but I’m not sure which transformations would help me in this case.

I made a smaller version of the project using Docker and published it to GitHub here: GitHub - brunobastosg/grafana-elasticsearch-weighted-average-attempt

If anyone knows how to help and want to play with the data, just clone the repo and run docker compose up.

EDIT: just found out that Elasticsearch can calculate the weighted averages (see below). Is there a way I can use this in Grafana?

POST /grades/_search
{
  "size": 0,
  "aggs": {
    "students": {
      "terms": {
        "field": "studentName",
        "size": 10
      },
      "aggs": {
        "weighted_grade": {
          "weighted_avg": {
            "value": {
              "field": "grade"
            },
            "weight": {
              "field": "weight"
            }
          }
        }
      }
    }
  }
}
2

You can try the add field from calculation transformation, and choose binary operation, you will need to do this multiple times to get all the required calcs, then you can do the individual calculations, multiply by the weights, add them together, divide by the sum. then you can use the organize fields transformation, to hide all the temporary calculation results, and only show the ones you are interested in.

2 Likes
3

Hi, thanks for your reply. Could you elaborate? I’m not sure I get it.

First, I add a “Add field from calculation”, binary operation, and create a new field called “gradeTimesWeight”, which is of course grade * weight.

Then I would need to sum all gradeTimesWeight and also all weight, and divide the former by the latter. But how can I do that?

EDIT: just got it working, see transformations needed below.

1 - Add field from calculation, binary type, multiplying grade * weight. Name the field gradeTimesWeight.
2 - Reduce, mode “Reduce fields”, calculations “Total”
3 - Add field from calculation, binary type, dividing gradeTimesWeight by weight.

This would get the weighted average for all students. But now I can adapt the solution for my needs.

1 Like