PowerBI Deneb Vega-lite: impute missing values on y-axis

Question

I´m showing an "population" pyramid by age-groups (Altersgruppe). In my dataset there are some missing values for different agegroups for male (männlich) and female (weiblich).

Link to File

I´d like to impute values (KPI-Set Display=0) for missing agegroup-entries age group values :

"N/A","0-4","5-9","10-14","15-19","20-24","25-29","30-34","35-39","40-44","45-49","50-54","55-59","60-64","65-69","70-74","75-79","80-84","85-89","90-94","95-99","100+"

Since my data set contains around 2 million entries, adding the missing data sets would increase the data volume to 7 million and cause a problem with performance

the result should look like this:

Answer 1

Create a measure as follows:

Measure = SUM(Daten[KPI-Set Display])+0

Add the following fields to the Deneb field well. Be careful to chose from the correct highlighted tables.

Update your spec to this:

 {
  "data": {"name": "dataset"},
  "transform": [
    {
      "calculate": "datum['Geschlecht'] == 'männlich' ? 'männlich' : datum['Geschlecht'] == 'weiblich' ? 'weiblich' : 'unbekannt'",
      "as": "gender"
    },
    {
      "aggregate": [
        {
          "op": "sum",
          "field": "Measure",
          "as": "Measure"
        }
      ],
      "groupby": [
        "Sortierung",
        "Altersgruppe",
        "gender"
      ]
    },
    {
      "joinaggregate": [
        {
          "op": "max",
          "field": "Measure",
          "as": "MaxVal"
        }
      ]
    }
  ],
  "spacing": 0,
  "hconcat": [
    {
      "width": 90,
      "height": 230,
      "transform": [
        {
          "filter": {
            "field": "gender",
            "equal": "weiblich"
          }
        }
      ],
      "title": "",
      "layer": [
        {
          "mark": {
            "type": "bar",
            "tooltip": true
          },
          "encoding": {
            "y": {
              "field": "Altersgruppe",
              "axis": null,
              "sort": {
                "field": "Sortierung",
                "order": "descending"
              }
            },
            "x": {
              "aggregate": "sum",
              "field": "Measure",
              "title": "weiblich",
              "scale": {
                "domainMax": {
                  "expr": "data('data_0')[0].MaxVal"
                }
              },
              "sort": "descending"
            }
          }
        }
      ]
    },
    {
      "width": 15,
      "height": 230,
      "layer": [
        {
          "mark": {
            "type": "text",
            "align": "center"
          },
          "encoding": {
            "y": {
              "field": "Altersgruppe",
              "type": "ordinal",
              "axis": null,
              "sort": {
                "field": "Sortierung",
                "order": "descending"
              }
            },
            "text": {
              "field": "Altersgruppe",
              "type": "ordinal"
            }
          }
        }
      ]
    },
    {
      "width": 90,
      "height": 230,
      "transform": [
        {
          "filter": {
            "field": "gender",
            "equal": "männlich"
          }
        }
      ],
      "title": "",
      "layer": [
        {
          "mark": {
            "type": "bar",
            "tooltip": true
          },
          "encoding": {
            "y": {
              "field": "Altersgruppe",
              "axis": null,
              "sort": {
                "field": "Sortierung",
                "order": "descending"
              }
            },
            "x": {
              "aggregate": "sum",
              "field": "Measure",
              "title": "männlich",
              "scale": {
                "domainMax": {
                  "expr": "data('data_0')[0].MaxVal"
                }
              },
              "sort": "ascending"
            }
          }
        }
      ]
    }
  ]
}