CODEWITHSUNDEEP
powerbivega-lite
cwollett
cwollett

Reputation: 66

Custom Average Calculation in Vega-Lite

I would like assistance figuring out whether it is possible to create a custom aggregation using vega-lite.

My data consists of 5 columns:

My current working version of this displays the data as above, but I would like to add a tick with the average grouped by question section & question. I am working in Power BI and while I have a measure for that calculation, using it in the vega-lite visual causes it to just return the CODE value as it's part of a "table" with that granularity.

The math formula for what I want is SUM([CODE] * [Number of Responses])/[Number of Responses] for each Question Section/Question. However, I do not see a way to use the aggregate transform in vega-lite to use sum in this way.

Data sample included below in case you do not want to click through to the vega-lite editor:

"data": {
  "values": [{
    "Question Section": "Instructor Evaluation",
    "Question": "Instructor provided timely feedback",
    "CODE": 1,
    "CODE_TEXT": "Strongly Disagree",
    "Number of Responses": 5
  },{
    "Question Section": "Instructor Evaluation",
    "Question": "Instructor provided timely feedback",
    "CODE": 2,
    "CODE_TEXT": "Disagree",
    "Number of Responses": 1
  },{
    "Question Section": "Instructor Evaluation",
    "Question": "Instructor provided timely feedback",
    "CODE": 3,
    "CODE_TEXT": "Neutral",
    "Number of Responses": 9
  },{
    "Question Section": "Instructor Evaluation",
    "Question": "Instructor provided timely feedback",
    "CODE": 4,
    "CODE_TEXT": "Agree",
    "Number of Responses": 12
  },{
    "Question Section": "Instructor Evaluation",
    "Question": "Instructor provided timely feedback",
    "CODE": 5,
    "CODE_TEXT": "Strongly Agree",
    "Number of Responses": 12
  }]
}

Upvotes: 0

Views: 20

Answers (1)

gmw
gmw

Reputation: 151

You can aggregate with the transform key. This may help:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {
    "values": [
      {"w": 1,"n": 5},
      {"w": 2,"n": 1},
      {"w": 3,"n": 9},
      {"w": 4,"n": 12},
      {"w": 5,"n": 12}
    ]
  },
  "transform": [
    {"calculate": "datum.w * datum.n", "as": "wn"}
  ],
  "layer": [
    {
      "mark": "point",
      "encoding": {
        "x": {"field": "w", "type": "quantitative"},
        "size": {"field": "n", "type": "quantitative"}
      }
    },
    {
      "transform": [
        {
          "aggregate": [
            {"op": "sum", "field": "n", "as": "N"}, 
            {"op": "sum", "field": "wn", "as": "mN"}
          ]
        },
        {"calculate": "datum.mN / datum.N", "as": "m"}
      ],
      "mark": "rule",
      "encoding": {"x": {"field": "m", "type": "quantitative"}}
    }
  ]
}

Upvotes: 2

Related Questions