Power BI 无公式实现帕累托图表

wujunmin

发布于 2025-04-23 03:28:04

7900

代码可运行

文章被收录于专栏：wujunminwujunmin

运行总次数：0

代码可运行

帕累托分析（Pareto Analysis），也被称为80/20法则、关键少数法则，是一种常用的管理工具，用于识别和处理影响业务的主要因素。

看到李伟坚老师在Excel使用Vega实现了花式帕累托（参考：Excel 零公式实现高级帕累托图表），我决定在Power BI也试试。只需要将维度和指标拖入Deneb（免费，支持vega）视觉对象：

无需手动计算累计占比，即得到：

原理是vega-lite，维度和指标放入Deneb后，画布上点击...-编辑-空白vega-lite，粘贴以下代码，即可复现：

{
  "data": {"name": "dataset"},
  "transform": [
    {
      "window": [{"op": "sum", "field": "KPI", "as": "total"}],
      "frame": [null, null]
    },
    {
      "window": [
        {"op": "sum", "field": "KPI", "as": "cumulative_sum"},
        {"op": "rank", "as": "rank"}
      ],
      "sort": [{"field": "KPI", "order": "descending"}],
      "frame": [null, 0]
    },
    {
      "calculate": "datum.cumulative_sum/datum.total",
      "as": "cumulative_percent"
    }
  ],
  "layer": [
    {
      "mark": {
        "type": "bar",
        "opacity": 0.3,
        "tooltip": true,
        "cornerRadius": 100,
        "cornerRadiusEnd": 100,
        "color": "lightgray",
        "size": 12
      },
      "encoding": {
        "y": {
          "field": "KPI",
          "type": "quantitative",
          "axis": null
        }
      }
    },
    {
      "mark": {
        "type": "bar",
        "tooltip": true,
        "cornerRadius": 100,
        "cornerRadiusEnd": 100,
        "color": "deepskyblue",
        "size": 25
      },
      "encoding": {
        "y": {
          "field": "KPI__highlight",
          "type": "quantitative",
          "axis": null
        },
        "opacity": {
          "condition": {
            "test": {"field": "__selected__", "equal": "off"},
            "value": 0
          },
          "value": 1
        }
      }
    },
    {
      "mark": {
        "type": "line",
        "tooltip": true,
        "color": "gray",
        "strokeWidth": 1.5,
        "interpolate": "monotone"
      },
      "encoding": {
        "y": {
          "field": "cumulative_percent",
          "type": "quantitative",
          "axis": null,
          "scale": {"domain": [0, 1]}
        }
      }
    },
    {
      "mark": {
        "type": "point",
        "tooltip": true,
        "color": "tomato",
        "size": 60,
        "filled": true,
        "opacity": 1
      },
      "encoding": {
        "y": {
          "field": "cumulative_percent",
          "type": "quantitative",
          "scale": {"domain": [0, 1]}
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "align": "center",
        "baseline": "bottom",
        "dy": -5,
        "color": "black",
        "fontSize": 14
      },
      "encoding": {
        "y": {
          "field": "KPI__highlight",
          "type": "quantitative",
          "axis": null
        },
        "text": {
          "field": "KPI__highlight",
          "format": ".0f"
        },
        "opacity": {
          "condition": {
            "test": {"field": "__selected__", "equal": "off"},
            "value": 0
          },
          "value": 1
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "align": "center",
        "baseline": "middle",
        "dy": -10,
        "dx": 0,
        "color": "gray",
        "fontSize": 12,
        "fontWeight": "bold"
      },
      "encoding": {
        "y": {
          "field": "cumulative_percent",
          "type": "quantitative"
        },
        "text": {
          "field": "cumulative_percent",
          "format": ".1%"
        }
      }
    }
  ],
  "encoding": {
    "x": {
      "field": "城市",
      "type": "nominal",
      "axis": {
        "title": null,
        "labelFontSize": 14,
        "labelAngle": 0,
        "labelPadding": 10
      },
      "sort": {
        "field": "KPI",
        "order": "descending"
      }
    }
  },
  "resolve": {
    "scale": {
      "y": "independent"
    }
  },
  "config": {
    "axis": {
      "grid": false,
      "domain": false,
      "labels": true,
      "ticks": false,
      "title": null
    },
    "axisY": {
      "labels": false,
      "title": null
    },
    "view": {
      "stroke": "transparent"
    }
  }
}