如何使用DAX查询或汇总表生成瀑布图所需的汇总表格

原表结构 #

你的月度销售表基础结构如下：

Acc_num Month cat_A cat_B balance_1 balance_2 etc
73836  Jan-25 abc  xyz   162     272

已通过DAX创建了current_balance、attrition_balance、balance_year_ago、growth等自定义字段。

目标结构 #

需要生成符合瀑布图要求的汇总表：

Category balances
Exist_bal.  27800
Growth.     1890
Attrition.   -2829
New.         1719
Total.       30000

方法1：创建DAX计算表 #

直接在Power BI或Tabular模型中创建计算表，代码如下：

瀑布图汇总表 = 
-- 定义各分类的余额数值
VAR ExistBal = SUM('销售表'[balance_year_ago])
VAR GrowthAmt = SUM('销售表'[growth])
-- 注意：若attrition_balance本身为负数（代表余额减少），可去掉负号
VAR AttritionAmt = -SUM('销售表'[attrition_balance])
-- 计算新增客户贡献的余额，或用标记新增客户的字段直接求和
VAR NewAmt = SUM('销售表'[current_balance]) - ExistBal - GrowthAmt - AttritionAmt
VAR TotalAmt = SUM('销售表'[current_balance])

-- 合并各分类行成最终表
RETURN
UNION(
    ROW("Category", "Exist_bal", "balances", ExistBal),
    ROW("Category", "Growth", "balances", GrowthAmt),
    ROW("Category", "Attrition", "balances", AttritionAmt),
    ROW("Category", "New", "balances", NewAmt),
    ROW("Category", "Total", "balances", TotalAmt)
)

关键说明 #

• Attrition数值方向：如果attrition_balance存储的是流失金额的正数，需加负号让它在瀑布图中显示为向下的减少项；若字段本身已为负数，直接使用SUM('销售表'[attrition_balance])即可。
• New字段优化：若模型中有标记新增客户的字段（如Is_New），可替换NewAmt的计算逻辑为：

  VAR NewAmt = SUMX(FILTER('销售表', '销售表'[Is_New] = TRUE()), '销售表'[current_balance])
  

• 时间范围限定：如果需要针对特定月份计算，可在VAR中添加筛选条件，例如：

  VAR ExistBal = CALCULATE(SUM('销售表'[balance_year_ago]), '销售表'[Month] = MAX('销售表'[Month]))
  

方法2：使用DAX查询生成结果 #

在Power BI的“外部数据”中选择“空白查询”，进入“高级编辑器”替换为以下DAX查询，可直接生成目标结构的表：

EVALUATE
VAR ExistBal = SUM('销售表'[balance_year_ago])
VAR GrowthAmt = SUM('销售表'[growth])
VAR AttritionAmt = -SUM('销售表'[attrition_balance])
VAR NewAmt = SUM('销售表'[current_balance]) - ExistBal - GrowthAmt - AttritionAmt
VAR TotalAmt = SUM('销售表'[current_balance])
RETURN
UNION(
    ROW("Category", "Exist_bal", "balances", ExistBal),
    ROW("Category", "Growth", "balances", GrowthAmt),
    ROW("Category", "Attrition", "balances", AttritionAmt),
    ROW("Category", "New", "balances", NewAmt),
    ROW("Category", "Total", "balances", TotalAmt)
)

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