預算模式----DAX學習筆記

01-26

銷售金額對一家公司來說是每天在發生的,而預算金額是很難精確到每天,企業預算籌劃一般是以年為單位,最多細分到月級別.因為銷售金額與預算金額存在時間單位上的顆粒度不統一,本文學習心得正是解決預算模型時間上的顆粒度不統一的問題.

做銷售預算,至少得從數據資料庫抽取三張表,時間表,銷售表,產品表,外加企業規划出的一張產品銷售預算表.如圖所示:

由於銷售表與預算表都是事實表,在DAX數據模型中,事實表與事實表之間不能發生直接的關聯,而是得通過維度表做為中轉站一般,取得間接的關係.如圖所示:

在模型中事實表永遠處於"多"對"一"關係中的多的一方,而維度表處於"一"的一方.日期表與數據源用日期欄位關聯,產品表與預算表或數據源之間通過產品欄位發生間接關係.由於日曆表只有一組日期欄位,至於月份,季度,工作日等欄位可以通過如下公式產生,這些DAX公式其實和工作表函數差不多,熟悉excel工作表函數的童鞋一定不陌生.

銷售總額無非是每天銷售金額的加總,公式很簡單:

銷售總額:=SUM([銷售金額])

接下來使用ISFILTERED函數解決預算總額與銷售總額在時間單位上不統一的問題.也就是當返回數據透視表時,假如存在時間上預算與實際銷售顆粒度不統一時,返回TRUE,單位統一時返回FALSE.以此做為後面IF語句的分支,構造一個複雜公式用來解決時間顆粒度不同時,做正確的預算分配.因此是一個過度型的度量公式.公式如下:

預算粒度:=ISFILTERED(日曆表[Date])||ISFILTERED(日曆表[月份])||ISFILTERED(日曆表[季度])

ISFILTERED按官方解釋 ISFILTERED=(<columnname>),當指定列上有直接篩選器時返回true

這個解釋很抽像,難以理解.對於DAX模型來說,構造一組公式類似於暗箱操作,有時很難弄懂公式背後的數據運作機制,不知道自己寫的公式是不是返回了自己所需要的數據,因此最好的辦法就是放到數據透視表中或是返回鏈接表中觀察.

構造一個相對簡單的公式用來觀察ISFILTERED函數的運算機制:度量值 1:=ISFILTERED(數據源[產品])

拖放進數據透視表中進行觀察:

很容易觀察到切片器進行篩選時,度量1的數值返回了TRUE.不止於切片器的篩選,透視表中其它元素造成對度量值1的間接篩選,同樣會從false變成true.比如下圖:

雖然切片器不再對度量值1發生篩選,但由於行區域的產品欄位對度量值1發生了接間的篩選,同樣返回了true.可是總計卻又是false.因為度量值 1:=ISFILTERED(數據源[產品])是對數據源[產品欄位的構造.總計是原封不動的,所以返回了false,但是在行區域被襯衫,自行車更小的粒度篩選了,所以返回true.假定把產品表中的產品欄位放入行區域,那就是無效的,依然返回false.可以多觀察幾次,構造更複雜的邏輯條件的ISFILTERED函數公式進行觀察,就比較容易弄懂其中原理.

接著往下,由於已經構造了一個過度用度量值:預算粒度.利用其返回true或false的特性構造if分支語句.返回false,也就是顆粒度統一時,則預算度量將返回預算總額。如果返回true,顆粒度不同時，則必須使用分配演算法計算預算，這取決於公司實際需求.在此示例中，您為該年度的每個工作日分配相同的預算。每年，您可以計算當前過濾器的工作日與當年的總工作日之間的比率。SUMX函數在所選擇的年份中迭代，並且每年使用模擬年度粒度關係的FILTER來檢索相應的預算。公式如下:

預算總額:=IF([預算粒度],

SUMX(VALUES(日曆表[年份]),

CALCULATE(COUNTROWS(日曆表),

日曆表[工作日]=TRUE)/CALCULATE(COUNTROWS(日曆表),ALLEXCEPT(日曆表,日曆表[年份]),日曆表[工作日]=TRUE)*CALCULATE(SUM(預算表[預算金額]),FILTER(ALL(預算表[年份]),預算表[年份]=日曆表[年份]))

SUM(預算表[預算金額]))

在這裡需要說明一下ALLEXCEPT函數,由於使用的少,也許不少人比較陌生,如果是ALL函數,相信學習DAX的童鞋中很多人都熟悉,刪除一切篩選帶來的影響,ALLEXCEPT函數在這裡的意思,除了日曆表中的年份欄位可能會受到篩選器的影響,除此之外,整個表所有欄位都不受篩選器的影響.

差異額無非是預算總額與銷售總額之間的比例.使用DIVIDE函數,避免除以0的情況.

差異額:=DIVIDE([銷售總額]-[預算總額],[預算總額])

返回數據透視表,現在可以觀察為每個季度每個月甚至於每個工作日月所做的預算KPI.

請注意列區域一定得使用產品表中產品欄位,其它表中的產品欄位是無效的,因為產品表處於兩個事實表中的一端,而篩選上下文能夠自動從一端向兩個多端傳遞(也就是兩個事實表中傳遞關係).

只要您想將預算分配到不同的粒度，您就可以使用這種技術。如果分配中涉及更多的表，則需要使用更複雜的模式.

用例

您可以使用預算模式，只要您擁有一個粒度數據的表，並且您希望根據滿足業務需求的分配演算法將數字分配到不同的粒度。

固定分配預算

如果您想將每年預算平均分配到每月預算中，則使用固定分配。例如，您將年值除以12以獲取月度值，或者將年值除以365（或閏年的366），然後將結果乘以每月的天數。在這兩種情況下，您都有一個確定性的分配，僅依賴於日曆。

基於歷史數據的預算分配

您可能希望使用歷史數據將預算分配給不能作為預算級別使用的屬性。例如，如果您有按產品類別定義的預算，則可以根據產品的銷售額與上一年的相應產品類別之間的銷售額進行分配。您可以同時分配多個屬性的預算，例如，也可以在日期的基礎上獲得基於先前銷售額的季節性分配。

完整模式下所需要的表:

數據模型包含與日期，區域和產品表關係的銷售表。如圖5所示，預算表沒有任何物理關係，即使它與日曆表中（月度級別）和產品表中（類別級別）具有邏輯關係。(雖然預算表中的類別與產品表中的類別具有邏輯關係,但兩列都存在重複行,所以建立不了物理關係,只有通過filter ,values建立虛擬的關係)

對於數據模型中的每個邏輯關係，在預算表中需增加一列年份月份數。以簡化預算和日期表之間邏輯關係的計算。在日曆表中同樣如此.

先求出銷售表中訂單總量

訂單總量:=SUM([訂單數量])

對於預算表中的預計銷售總量的計算分配和基本模式相同,在和訂單總量處於顆粒度相同時,設置一組公式,顆粒度不相同時又是另一組具有複雜分配方案的公式,只是比基本模式要涉及的表要多.

預算有效:=COUNTROWS(銷售表)=CALCULATE(COUNTROWS(銷售表),

ALL(銷售表),

VALUES(日期表[月份]),

VALUES(產品表[類別]))

如上圖所示,預算有效返回True,當放進數據透視表中,進行複雜篩選肯定會發生顆粒同不相同時返回false的情況.

預算計算:=CALCULATE(SUM(預算表[預算]),

FILTER(ALL(預算表[年份月份]),

CONTAINS(VALUES(日期表[年份月份]),

日期表[年份月份],

預算表[年份月份])),

FILTER(ALL(預算表[類別]),

CONTAINS(VALUES(產品表[類別]),

產品表[類別],

預算表[類別])))

計算上年銷售同比用如下公式:

訂單總量YOY:=CALCULATE([訂單總量],SAMEPERIODLASTYEAR(日期表[日期]))

再接著往下構造如下公式

分配比率過渡:=[訂單總量YOY]/CALCULATE([訂單總量YOY],ALLEXCEPT(產品表,產品表[類別]),ALL(日期表[日期]),VALUES(日期表[年份月份]),ALL(地域表))

需要注意的是ALLEXCEPT從具有與預算有邏輯關係的表格中刪除具有比預算中可用列數更高的粒度的列的過濾器.但是對於需要從標記為日期表的表中刪除過濾器時，請使用ALL / VALUES技術代替ALLEXCEPT。經過測試,如果不用ALL/VALUES替代,會在數據透視表小計匯總層面發生錯誤,也就是一片空白,沒有數據.

現在有了預算計算與分配比率過渡兩個度量公式,就可以定義當預算有效返回false時的預算分配方案.公式如下

粒度不同預算分配方案:=SUMX(CROSSJOIN(VALUES(日期表[年份月份]),VALUES(產品表[類別])),[分配比率過渡]*[預算計算])

CROSSJOIN是一個表格型函數.結果返回出一個笛卡爾積的交叉表,也就是表格與表格之間的乘積.語法:CROSSJOIN(<表格1>,<表格2>,<表格3>.....)

需要注意的是<表格>參數中的列名在所有表中都必須是不同的,否則返回錯誤,公式無效.

對於ROSSJOIN(VALUES(日期表[年份月份]),VALUES(產品表[類別]))在這裡產生的一個新表格或許會覺得很難理解.可以返回到逆向鏈接表中觀察.

通過觀察,[年份月份]與[類別]兩列相乘產生了一個更加細密的新表格.從而使得預算分配在更小的顆粒度上進行分配.

最終的預算分配方案於是可以用一個簡單的IF語句就可以完成,當返回TRUE時,顆粒度相同時,通常預算的規劃在時間上比較粗,在年份這個匯總層面與銷售表中的銷售是相同的,而再往下細分配到月份,星期天,每天,一般就返回false了.

預算分配:=IF([預算有效],[預算計算],[粒度不同預算分配方案])

預算與銷售數量的差異率

差異率:=DIVIDE([訂單總量]-[預算分配],[預算分配])

所有公式完成,只需要差異率,預算分配,銷售總量三個度量值,其它都可以隱藏,返回到數據透視表,就可以進行不同的數據切片.從不同角度調查自己所需要的數據.

如需下載文件請到QQ交流群553270834

帥的人都關注了EasyCharts團隊^..^~

QQ交流群：553270834

微信公眾號：EasyCharts

更多信息敬請查看: http://easychart.github.io/post/Easycharts/