PowerBI Deneb Vega-lite: 在y轴上填充缺失值

huangapple go评论89阅读模式
英文:

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

问题

我正在展示一个按年龄组(Altersgruppe)划分的“人口金字塔”。在我的数据集中,男性(männlich)和女性(weiblich)的不同年龄组中存在一些缺失值。

文件链接

我想要为缺失的年龄组条目(KPI-Set Display=0)进行插补。
年龄组数值:

"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+"

由于我的数据集包含约200万条目,添加缺失数据集将使数据量增加到700万,并导致性能问题。

结果应该看起来像这样:
PowerBI Deneb Vega-lite: 在y轴上填充缺失值

英文:

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

PowerBI Deneb Vega-lite: 在y轴上填充缺失值

the result should look like this:
PowerBI Deneb Vega-lite: 在y轴上填充缺失值

答案1

得分: 1

以下是翻译好的部分:

创建如下度量:

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

将以下字段添加到Deneb字段区域。务必从正确的突出显示的表中进行选择。

更新您的规格如下:

 {
  "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"
            }
          }
        }
      ]
    }
  ]
}
英文:

PowerBI Deneb Vega-lite: 在y轴上填充缺失值

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.

PowerBI Deneb Vega-lite: 在y轴上填充缺失值

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"
            }
          }
        }
      ]
    }
  ]
}

huangapple
  • 本文由 发表于 2023年8月10日 17:40:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/76874505.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定