Search code examples
vega-litedeneb

Vega-Lite - Different aggregation on totals based on row header

In Vega-Lite I'm trying to create a total column where the row header 'forecast' is just a sum of my row values whereas 'percentage' is an average of my row values. I managed to to get it to work partially with the joinaggregate but it seems that the values are being repeated several times?

enter image description here

Here is my code:

{
  "data": {
    "values": [
      {
        "Customer": "Kevin N.V.",
        "Product": "PH114",
        "YearMonthText": "2024.01",
        "Column": "Forecast",
        "_Value": 24
      },
      {
        "Customer": "Kevin N.V.",
        "Product": "PH114",
        "YearMonthText": "2024.01",
        "Column": "Percentage",
        "_Value": 0.9
      },
      {
        "Customer": "Kevin N.V.",
        "Product": "PH114",
        "YearMonthText": "2024.02",
        "Column": "Forecast",
        "_Value": 74
      },
      {
        "Customer": "Kevin N.V.",
        "Product": "PH114",
        "YearMonthText": "2024.02",
        "Column": "Percentage",
        "_Value": 0.75
      },
      {
        "Customer": "Kevin N.V.",
        "Product": "PH878",
        "YearMonthText": "2024.01",
        "Column": "Forecast",
        "_Value": 744
      },
      {
        "Customer": "Kevin N.V.",
        "Product": "PH878",
        "YearMonthText": "2024.01",
        "Column": "Percentage",
        "_Value": 0.05
      },
      {
        "Customer": "Kevin N.V.",
        "Product": "PH878",
        "YearMonthText": "2024.02",
        "Column": "Forecast",
        "_Value": 4
      },
      {
        "Customer": "Kevin N.V.",
        "Product": "PH878",
        "YearMonthText": "2024.02",
        "Column": "Percentage",
        "_Value": 0.07
      },
      {
        "Customer": "Klaas N.V.",
        "Product": "PH200",
        "YearMonthText": "2024.01",
        "Column": "Forecast",
        "_Value": 77
      },
      {
        "Customer": "Klaas N.V.",
        "Product": "PH200",
        "YearMonthText": "2024.01",
        "Column": "Percentage",
        "_Value": 0.65
      },
      {
        "Customer": "Klaas N.V.",
        "Product": "PH200",
        "YearMonthText": "2024.02",
        "Column": "Forecast",
        "_Value": 80
      },
      {
        "Customer": "Klaas N.V.",
        "Product": "PH200",
        "YearMonthText": "2024.02",
        "Column": "Percentage",
        "_Value": 0.8
      },
      {
        "Customer": "Klaas N.V.",
        "Product": "PH527",
        "YearMonthText": "2024.01",
        "Column": "Forecast",
        "_Value": 4
      },
      {
        "Customer": "Klaas N.V.",
        "Product": "PH527",
        "YearMonthText": "2024.01",
        "Column": "Percentage",
        "_Value": 0.77
      },
      {
        "Customer": "Klaas N.V.",
        "Product": "PH527",
        "YearMonthText": "2024.02",
        "Column": "Forecast",
        "_Value": 88
      },
      {
        "Customer": "Klaas N.V.",
        "Product": "PH527",
        "YearMonthText": "2024.02",
        "Column": "Percentage",
        "_Value": 0.9
      }
    ]
  },
  "facet": {
    "row": {"field": "Product", "title": null, "header": {"labelFontSize": 14}}
  },
  "spec": {
    "layer": [
      {"mark": {"type": "rect"}},
      {
        "mark": {"type": "text", "fontSize": 12},
        "encoding": {
          "y": {"field": "Column", "title": null},
          "x": {
            "field": "YearMonthText",
            "title": null,
            "axis": {"orient": "top"}
          },
          "text": {"field": "_Value", "type": "quantitative"}
        }
      },
      {
        "name": "Totals",
        "transform": [
          {
            "joinaggregate": [
              {
                "op": "sum",
                "field": "_Value",
                "as": "_New_Value_Sum"
              },
                            {
                "op": "mean",
                "field": "_Value",
                "as": "_New_Value_Mean"
              }
            ],
            "groupby": [
              "Product",
              "Column"
            ]
          },
          {
            "calculate": "datum.Column === 'Percentage' ? format( datum._New_Value_Mean, '.0%') : datum.Column === 'Forecast' ? format( datum._New_Value_Sum, ',.0f') : format( datum._New_Value_Sum, ',.0f')",
            "as": "_New_Value_Formatted"
          }
        ],
        "mark": {
          "type": "text",
          "align": "left",
          "fontSize": 14,
          "fontWeight": "bold",
          "dx": 80,
          "color": "black"
        },
        "encoding": {
          "y": {"field": "Column", "title": null},
          "x": {
            "field": "YearMonthText",
            "title": null,
            "axis": {"orient": "top"}
          },
          "text": {"field": "_New_Value_Formatted", "type": "nominal"}
        }
      }
    ]
  },
  "resolve": {"axis": {"x": "independent", "y": "independent"}}
}

Is there someone who can help me solve this problem?

Solution

  • In your last Total layer:

    1.) Change joinaggregate to aggregate.

    2.) Remove entire X axis.

    3.) Adjust dx to 30 for example.