PowerBI_一分鐘學會利用ALLEXCPET分組計算（以計算門店開業前3天銷售金額為例）

simone331發表於2024-08-04

在某些特殊場景，我們往往需要去計算一些特定的組別的聚合資料

今天，就以計算門店開業前3天的銷售情況，來學習一下，利用計算列和DAX度量值，兩種快捷計算此類問題的方案。

一：XMIND

二：示例資料

2.1 示例資料列說明

為了方便驗證和更清晰的檢查結果，資料來源只用了三列，分別是3個門店，分別為A，B，C，日期列，以及一列模擬的營業額資料。

2.2 計算目標

目標就是利用DAX或者計算列，計算出，每一家門店實際產生銷售的前三天的營業額之和

三：計算列方案

首先，我們用計算列方案來解決。

遇到此類問題，最快捷或者說最直接的方式就是透過計算列給我們的每行資料進行資料標註，再透過CALCULATE計算器的篩選器，去篩選我們想要的標籤，再得到結果

下方的計算列方案，第一步就是透過MIN函式求得最小得日期，由於我們是計算每個門店單獨得最小日期得銷售之和，所以這裡使用了ALLEXCEPT，去除門店列得篩選，總而得到了每一個門店得開業前三天得資料標籤。

最後，透過CALCULATE函式，對符合我們要求的資料，進行求和，便得出我們的結果。

具體DAX程式碼如下：

第一步，新增營業表的計算列

IsFirst3Days = 

VAR MinDate = CALCULATE(MIN('示例表'[日期]), ALLEXCEPT('示例表', '示例表'[門店]))

RETURN

IF(

    '示例表'[日期] >= MinDate && '示例表'[日期] <= MinDate + 2,

    "開業前三天門店日期",

    "其他"

)     

第二步，度量值計算營業額，利用第一步的標籤來篩選並計算

 IsFirst3Days_營業額= 

    CALCULATE(SUM('示例表'[營業額]),

            '示例表'[IsFirst3Days]="開業前三天門店日期")

　　

四：DAX方案

那麼有沒有更加方便，或者說一步解決問題的方案呢？

當然有，我們來看看純DAX的解決思路。

首先，思路和計算列方案是一樣的，我們也是利用標籤的形式去篩選表，然後直接對符合條件的行進行求和，即可得到目標值

下方DAX中，首先利用FILTER函式，對示例表進行篩選，配合ALLEXCEPT函式，去除表篩選條件，最後直接用CALCULATE函式進行聚合。

有一點不一樣的是，計算列方案時，我們用了IF去進行判斷，DAX方案中，我們用了並列條件，即同時滿足這兩個條件的行我們保留，小知識點，在DAX中我們會使用 "&&" 去表達我們的 "且" 這個概念。

最後，我們可以看到，兩種方案的結果是一樣的。

純DAX方案：

TotalSalesFirst3Days = 

CALCULATE(

    SUM('示例表'[營業額]),

    FILTER(

        '示例表',

        '示例表'[日期] >= CALCULATE(MIN('示例表'[日期]), ALLEXCEPT('示例表', '示例表'[門店])) &&

        '示例表'[日期] <= CALCULATE(MIN('示例表'[日期]), ALLEXCEPT('示例表', '示例表'[門店])) + 2

    )

)

看到這裡，有些朋友可能會問，為什麼會在這裡介紹兩種方案，而不只介紹DAX呢，明明DAX一步就可以解決問題？

因為，即便計算組，會浪費記憶體，在大的資料集表中有相當大的效能限制，但是，在某些緊急情況（來不及構思DAX）或者某些特殊場景下，我們首先需要解決問題，我們是鼓勵多一些的解決問題思路。

所以，在效能充足時，不必糾結解決方式，解決問題就好，效能最佳化的事情，我們放在後面解決也不遲。

五：關鍵DAX-ALLEXCEPT函式

5.1 語法

ALLEXCEPT(<table>, <column1>, <column2>, ...)

5.2 作用

ALLEXCEPT函式會返回一個表，該表刪除了所有篩選器，除了那些應用於指定列的篩選器。這在需要對某些列進行聚合計算時非常有用，而不受其他列的篩選條件影響。

5.3 使用場景

計算佔比：例如，你想計算某個城市在省份中的銷售佔比，可以使用ALLEXCEPT函式保留省份的篩選條件，移除其他篩選條件。

複雜的聚合計算：在進行復雜的聚合計算時，ALLEXCEPT可以幫助你控制哪些列的篩選條件被保留，哪些被移除，從而更靈活地進行資料分析。

5.4 其他類似函式

ALL：移除所有篩選器，返回整個表或列

ALL('銷售資料')

ALLSELECTED：移除所有篩選器，但保留使用者在視覺物件中選擇的篩選器

ALLSELECTED('銷售資料')

REMOVEFILTERS：移除指定列或表的篩選器。

REMOVEFILTERS('銷售資料'[省份])

KEEPFILTERS：保留現有篩選器，並在此基礎上新增新的篩選器。

CALCULATE([銷售量], KEEPFILTERS('銷售資料'[城市] = "成都"))

六：總結

實際業務中，這類根據特定分組，如小組，商品品類，具體門店等，進行統計的場景是很常見的，而今天講到的ALLEXCEPT函式的靈活使用，恰恰可以幫助我們解決這類問題

這類問題，包含了日期，分組佔比，分組排名等等一系列的場景，值得我們花時間去研究，後續如果有實際案例發生，我會做補充的案例說明。

ENJOY DAX

相關文章