Power BI Deneb(Vega Lite)：2023预测线及月份排序问题求助

DevOps 小满

2026-7-17

Power BI Deneb（Vega Lite）面积图问题：月份排序异常+预测虚线需求

我使用Power BI的Deneb可视化工具及Vega Lite语言制作了两张面积图，展示2022年和2023年某度量值的月度变化情况，目前存在两个问题/需求：

基于2022年完整数据和2023年前几个月数据，需从6月开始为2023年面积图添加虚线预测线（参考2022年12月峰值，预测2023年12月出现更高峰值）
图表中的月份未按自然顺序排列，需排查并修正

图表截图：

配套PBIX文件可用于测试，当前使用的Vega Lite代码如下：

{
  "data": {"name": "dataset"},
  "transform": [
    {
      "calculate": "format(datum['Leaves Count 2022']/1000,'0.1f')+'k'",
      "as": "l1"
    },
    {
      "calculate": "format(datum['Leaves Count 2023']/1000,'0.1f')+'k'",
      "as": "l2"
    },
    {
      "joinaggregate": [
        {
          "op": "max",
          "field": "Leaves Count 2022",
          "as": "l1max"
        },
        {
          "op": "max",
          "field": "Leaves Count 2023",
          "as": "l2max"
        },
        {
          "op": "min",
          "field": "Leaves Count 2022",
          "as": "l1min"
        },
        {
          "op": "min",
          "field": "Leaves Count 2023",
          "as": "l2min"
        }
      ]
    }
  ],
  "layer": [
    {
      "mark": {
        "type": "area",
        "line": {"color": "#063970"},
        "color": {
          "x1": 1,
          "y1": 1,
          "gradient": "linear",
          "stops": [
            {
              "offset": 0,
              "color": "white"
            },
            {
              "offset": 1,
              "color": "#063970"
            }
          ]
        }
      }
    },
    {
      "mark": {
        "type": "area",
        "line": {"color": "#2596be"},
        "color": {
          "x1": 1,
          "y1": 1,
          "gradient": "linear",
          "stops": [
            {
              "offset": 0,
              "color": "white"
            },
            {
              "offset": 1,
              "color": "#2596be"
            }
          ]
        }
      },
      "encoding": {
        "y": {
          "field": "Leaves Count 2022",
          "type": "quantitative"
        }
      }
    },
    {
      "mark": {
        "type": "circle",
        "size": 50,
        "fill": {
          "expr": "datum['Leaves Count 2023'] == datum.l2max ? 'red' : datum['Leaves Count 2023'] == datum.l2min ? 'green' : '#063970'"
        },
        "stroke": "white",
        "strokeWidth": 1
      },
      "encoding": {
        "x": {
          "field": "MONTH",
          "type": "ordinal"
        },
        "y": {
          "field": "Leaves Count 2023",
          "type": "quantitative"
        }
      }
    },
    {
      "mark": {
        "type": "circle",
        "size": 50,
        "fill": {
          "expr": "datum['Leaves Count 2022'] == datum.l1max ? 'red' : datum['Leaves Count 2022'] == datum.l1min ? 'green' : '#2596be'"
        },
        "stroke": "white",
        "strokeWidth": 1
      },
      "encoding": {
        "x": {
          "field": "MONTH",
          "type": "ordinal"
        },
        "y": {
          "field": "Leaves Count 2022",
          "type": "quantitative"
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "yOffset": -10,
        "size": 10,
        "color": {
          "expr": "datum['Leaves Count 2023'] == datum.l2max ? 'red' : datum['Leaves Count 2023'] == datum.l2min ? 'green' : '#000000'"
        },
        "fontWeight": {
          "expr": "datum['Leaves Count 2023'] == datum.l2max || datum['Leaves Count 2023'] == datum.l2min ? 'bold' : 'normal'"
        }
      },
      "encoding": {
        "text": {"field": "l2"},
        "opacity": {
          "condition": {
            "test": {
              "field": "MONTH",
              "equal": "off"
            },
            "value": 0.1
          },
          "value": 1
        },
        "y": {
          "field": "Leaves Count 2023",
          "type": "quantitative",
          "axis": null
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "yOffset": -10,
        "size": 10,
        "color": {
          "expr": "datum['Leaves Count 2022'] == datum.l1max ? 'red' : datum['Leaves Count 2022'] == datum.l1min ? 'green' : '#000000'"
        },
        "fontWeight": {
          "expr": "datum['Leaves Count 2022'] == datum.l1max || datum['Leaves Count 2022'] == datum.l1min ? 'bold' : 'normal'"
        }
      },
      "encoding": {
        "text": {"field": "l1"},
        "opacity": {
          "condition": {
            "test": {
              "field": "MONTH",
              "equal": "off"
            },
            "value": 0.1
          },
          "value": 1
        },
        "y": {
          "field": "Leaves Count 2022",
          "type": "quantitative",
          "axis": null
        }
      }
    },
    {
      "mark": {
        "type": "rule",
        "stroke": "red",
        "strokeDash": [3, 3]
      },
      "encoding": {
        "x": {
          "field": "MONTH",
          "type": "ordinal"
        },
        "y": {
          "field": "Leaves Count 2023",
          "type": "quantitative"
        }
      },
      "transform": [
        {"filter": "datum['Leaves Count 2023'] == datum.l2max"}
      ]
    },
    {
      "mark": {
        "type": "rule",
        "stroke": "red",
        "strokeDash": [3, 3]
      },
      "encoding": {
        "x": {
          "field": "MONTH",
          "type": "ordinal"
        },
        "y": {
          "field": "Leaves Count 2022",
          "type": "quantitative"
        }
      },
      "transform": [
        {"filter": "datum['Leaves Count 2022'] == datum.l1max"}
      ]
    }
  ],
  "encoding": {
    "x": {
      "field": "MONTH",
      "type": "ordinal",
      "axis": {"labelPadding": 0},
      "title": null
    },
    "y": {
      "field": "Leaves Count 2023",
      "type": "quantitative",
      "axis": null
    }
  }
}

解决方案

一、修正月份排序异常

原因

当前MONTH字段为文本类型，Vega Lite的ordinal类型会按文本字典序排序（例如"10"会排在"2"之前），导致月份顺序混乱。

解决方法（二选一）

方法1：Power BI数据模型预处理

在数据模型中新增数字类型的月份序号字段（如MonthNumber，取值1-12），保留原文本月份用于显示。修改Vega Lite的x轴编码：

"x": {
  "field": "MonthNumber",
  "type": "ordinal",
  "axis": {
    "labelExpr": "{'1':'1月','2':'2月','3':'3月','4':'4月','5':'5月','6':'6月','7':'7月','8':'8月','9':'9月','10':'10月','11':'11月','12':'12月'}[datum.label]",
    "labelPadding": 0
  },
  "title": null
}

方法2：Vega Lite内部转换排序

无需修改数据模型，在transform中添加月份序号计算并排序：

"transform": [
  // 保留原有transform内容
  {
    "calculate": "parseInt(datum.MONTH)",
    "as": "MonthNum"
  },
  {
    "sort": [{"field": "MonthNum"}],
    "window": [{"op": "row_number", "as": "sortIndex"}]
  }
],

同时修改x轴编码：

"x": {
  "field": "sortIndex",
  "type": "ordinal",
  "axis": {
    "labelExpr": "datum.MONTH",
    "labelPadding": 0
  },
  "title": null
}

二、添加2023年6月后的虚线预测线

通过生成预测数据点并绘制虚线实现，以下是基于2022年趋势比例的预测方案：

1. 更新transform逻辑，生成预测数据

在原有transform中添加以下步骤：

"transform": [
  // 保留原有transform内容
  // 计算2022年月度数据占当年峰值的比例
  {
    "calculate": "datum['Leaves Count 2022'] / datum.l1max",
    "as": "2022Ratio"
  },
  // 获取2023年已有的最高值，作为预测基准
  {
    "joinaggregate": [{"op": "max", "field": "Leaves Count 2023", "as": "l2CurrentMax"}]
  },
  // 设置2023年预测峰值（此处设为当前最高值的1.2倍，可按需调整）
  {
    "calculate": "datum.l2CurrentMax * 1.2",
    "as": "l2PredictedMax"
  },
  // 生成6-12月的预测月份序列
  {
    "sequence": {
      "start": 6,
      "stop": 13,
      "as": "PredictedMonth"
    }
  },
  // 关联2022年对应月份的比例
  {
    "lookup": "PredictedMonth",
    "from": {"data": "dataset", "key": "MonthNum", "fields": ["2022Ratio"]}
  },
  // 计算预测值
  {
    "calculate": "datum.l2PredictedMax * datum.2022Ratio",
    "as": "PredictedLeaves"
  }
]

2. 添加预测线图层

在layer数组末尾新增以下图层：

{
  "mark": {
    "type": "line",
    "stroke": "#063970",
    "strokeDash": [5, 3],
    "strokeWidth": 2
  },
  "encoding": {
    "x": {
      "field": "PredictedMonth",
      "type": "ordinal",
      "axis": null
    },
    "y": {
      "field": "PredictedLeaves",
      "type": "quantitative"
    }
  },
  "transform": [
    {"filter": "datum.PredictedMonth >=6"}
  ]
}

补充说明

预测逻辑可按需调整：比如用线性回归、移动平均等算法替代比例法，或直接指定预测点数值。
若需衔接2023年现有6月数据，需确保预测6月的数值与实际数据一致。

内容的提问来源于stack exchange，提问作者userrr