前言

本人不才匀奏，之前已經(jīng)簡單與大家聊過 Power BI 中的分組問題了鞭衩，近日朋友又問了一個實際工作中的問題，恰巧也與分組有關(guān)娃善，便整理之后论衍，與眾位朋友共享，再談 Power BI 分組的博大精深聚磺。

問題

需求

現(xiàn)在有一張事實表坯台，樣例如下

朋友想對各個產(chǎn)品ID的平均銷售額進行分組操作，分成以下8組

朋友的解決方案

不難看出瘫寝，事實表的每一行均為一張訂單蜒蕾，所以先要得到一張中間表每個產(chǎn)品ID平均銷售額的中間表。

于是朋友使用 Power BI 中的 DAX 構(gòu)建表的方式焕阿，構(gòu)建了一張中間表咪啡。公式如下：

中間表 =
SUMMARIZE ( '訂單', '訂單'[產(chǎn)品ID], "平均銷售額", AVERAGE ( '訂單'[銷售額] ) )

結(jié)果樣例如下：

P1_中間表.png

然后使用 DAX 增加計算列，公式如下：

分組列 =
SWITCH (
    TRUE (),
    '中間表'[平均銷售額] < 100, "小于等于100",
    '中間表'[平均銷售額] >= 100
        && '中間表'[平均銷售額] < 500, "100 ~ 500",
    '中間表'[平均銷售額] >= 500
        && '中間表'[平均銷售額] < 1000, "500 ~ 1000",
    '中間表'[平均銷售額] >= 1000
        && '中間表'[平均銷售額] < 2000, "1000 ~ 2000",
    '中間表'[平均銷售額] >= 2000
        && '中間表'[平均銷售額] < 3000, "2000 ~ 3000",
    '中間表'[平均銷售額] >= 3000
        && '中間表'[平均銷售額] < 4000, "3000 ~ 4000",
    '中間表'[平均銷售額] >= 4000
        && '中間表'[平均銷售額] < 5000, "4000 ~ 5000",
    '中間表'[平均銷售額] >= 5000, "大于等于5000"
)

之后就完成了數(shù)據(jù)準(zhǔn)備暮屡，得到了下表：

P2_計算列.png

之后僅需將分組列和產(chǎn)品ID的計數(shù)拖入表格即可得到結(jié)果撤摸。

P3_結(jié)果1.png

朋友的困擾

但是老板的需求要是這么簡單就好了，老板希望可以根據(jù)訂單日期進行篩選，但是現(xiàn)在的這個方法准夷，訂單日期的篩選完全無效钥飞。

P4_朋友的困擾.gif

解法

看過我之前博文的戰(zhàn)友應(yīng)該能有些思路，這類問題冕象，就應(yīng)該使用 DAX 來解決代承，我們不做計算列，不做中間表渐扮，全都應(yīng)該依靠 DAX 的模型能力论悴。應(yīng)了佐羅老師的一句名言：DAX 驅(qū)動可視化，非侵入式的模型設(shè)計∧孤桑現(xiàn)在且聽我娓娓道來膀估。

準(zhǔn)備

為了分組，當(dāng)然要準(zhǔn)備一張分組表耻讽，可用的方式有很多察纯，例如再導(dǎo)入一張分組表等等，此處使用 DAX 表達式针肥，構(gòu)建一張分組表饼记，公式如下：

分組表 =
DATATABLE (
    "組別", STRING,
    "最小值", INTEGER,
    "最大值", INTEGER,
    {
        { "< 100", -999999, 100 },
        { "100 ~ 500", 100, 500 },
        { "500 ~ 1000", 500, 1000 },
        { "1000 ~ 2000", 1000, 2000 },
        { "2000 ~ 3000", 2000, 3000 },
        { "3000 ~ 4000", 3000, 4000 },
        { "4000 ~ 5000", 4000, 5000 },
        { "> 5000", 5000, 999999 }
    }
)

其中 DATATABLE 函數(shù)用于構(gòu)建一張表，前半部分用于說明表結(jié)構(gòu)慰枕，后半部分用于填入數(shù)據(jù)具则。之后就得到了下表：

P5_構(gòu)建分組表.png

表達式詳解

準(zhǔn)備已成，那我們就可以構(gòu)建 DAX 表達式了具帮，分組僅用一個度量值博肋，表達式如下：

分組方式 =
VAR CurrentItem =
    SELECTEDVALUE ( '分組表'[組別] )  // 確定所在的組別
VAR LeftValue =
    CALCULATE ( MIN ( '分組表'[最小值] ), '分組表'[組別] = CurrentItem )  //將該組的最小值作為左值
VAR RightValue =
    CALCULATE ( MIN ( '分組表'[最大值] ), '分組表'[組別] = CurrentItem )  //將該組的最大值作為右值
VAR mid_table =
    ADDCOLUMNS (
        VALUES ( '訂單'[產(chǎn)品ID] ),
        "平均銷售額", CALCULATE ( AVERAGE ( '訂單'[銷售額] ) )
    )  // 按照產(chǎn)品ID構(gòu)造一張?zhí)摂M中間表
VAR ItemRange =
    FILTER ( mid_table, [平均銷售額] >= LeftValue && [平均銷售額] < RightValue )  // 過濾出屬于最初選定組別的虛擬表的子集
RETURN
    COUNTROWS ( ItemRange )

可能有些伙伴沒有看明白，我分布說明下：

1. 由于度量值最終會和組別一起使用蜂厅，那么首先要確定是針對那個組進行統(tǒng)計

VAR CurrentItem =
    SELECTEDVALUE ( '分組表'[組別] ) 

2. 然后確定了組別匪凡，就需要確定這一組的左值和右值，一邊后面用于計算

VAR LeftValue =
    CALCULATE ( MIN ( '分組表'[最小值] ), '分組表'[組別] = CurrentItem )  
VAR RightValue =
    CALCULATE ( MIN ( '分組表'[最大值] ), '分組表'[組別] = CurrentItem )

3. 之后構(gòu)造一張?zhí)摂M表掘猿，是按照產(chǎn)品ID求銷售額的平均值病游，行為類似上文中使用 SUMMARIZE 構(gòu)造的中間表

VAR mid_table =
    ADDCOLUMNS (
        VALUES ( '訂單'[產(chǎn)品ID] ),
        "平均銷售額", CALCULATE ( AVERAGE ( '訂單'[銷售額] ) )
    )  

4. 最后要統(tǒng)計出屬于該分組的一個子集，統(tǒng)計其行數(shù)就是產(chǎn)品ID的計數(shù)啦稠通。

VAR ItemRange =
    FILTER ( mid_table, [平均銷售額] >= LeftValue && [平均銷售額] < RightValue ) 
RETURN
    COUNTROWS ( ItemRange )

最后讓我們一起來看看效果礁遵。

P6_最終效果.gif

總結(jié)

最近都在寫分組，但是分組卻是工作中最常用的場景采记。戰(zhàn)友們?nèi)绻泄ぷ髦杏龅降挠腥さ姆纸M需求，歡迎在留言區(qū)留言政勃，我們再交流唧龄，尋找模型驅(qū)動可視化的邊界。