英文:
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万,并导致性能问题。
结果应该看起来像这样:
英文:
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).
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
答案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"
}
}
}
]
}
]
}
英文:
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"
}
}
}
]
}
]
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论