PBI Challenge #14 Thực Tế – Dynamic Cost Allocation

Table: FactExpenses 
Chứa thông tin chi phí của từng nhân viên theo ngày

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| Date        | date    |
| EmpID       | varchar |
| Amount      | int     |
+-------------+---------+

Table: DimEmployee
Liên kết nhân viên với vùng (Region) tương ứng.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| EmpID       | varchar |
| RegionID    | varchar |
+-------------+---------+

Table: DimRegion
chứa thông tin của các vùng.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| RegionID    | varchar |
| RegionName  | varchar |
+-------------+---------+

Table: AllocationRules
Chứa các quy tắc phân bổ chi phí từ vùng nguồn (Source) sang vùng đích (Target).
Percentage chỉ định tỷ lệ phần trăm chi phí của vùng nguồn được phân bổ cho vùng đích.

+----------------+---------+
| Column Name    | Type    | 
+----------------+---------+
| SourceRegionID | varchar |
| TargetRegionID | varchar | 
| Percentage     | float   | 
+----------------+---------+

Yêu cầu: Tính toán chi phí sau phân bổ cho từng vùng dựa trên các vùng được chọn trong báo cáo (Slicer). Hệ thống phải tự động tái phân bổ chi phí theo quy tắc:

• Nếu vùng đích (Target) được chọn, nó sẽ nhận phần chi phí được phân bổ từ vùng nguồn.
• Nếu vùng đích không được chọn, phần chi phí được phân bổ đó sẽ được giữ lại tại vùng nguồn.
• Tổng chi phí của tất cả các vùng hiển thị (bao gồm cả phần dư tại nguồn) cộng với chi phí gốc của các vùng bị ẩn phải luôn có tổng bằng tổng chi phí hệ thống.

Ví dụ 1:

Input

Bảng FactExpenses:
+------------+-------+--------+
| Date       | EmpID | Amount |
+------------+-------+--------+
| 2023-01-02 | E_A_1 | 1000   |
| ...        | ...   | ...    |
+------------+-------+--------+
(Tổng chi phí tính được cho các vùng: Region A=15000, B=15000, C=150000, D=15000, E=75000)

Bảng AllocationRules:
+----------------+----------------+------------+
| SourceRegionID | TargetRegionID | Percentage |
+----------------+----------------+------------+
| C              | A              | 0.50       |
| C              | B              | 0.50       |
| E              | A              | 0.70       |
| E              | D              | 0.30       |
+----------------+----------------+------------+

Tình huống: Người dùng chọn Region A, Region B, và Region D (Tất cả vùng đích).

Output:

+----------+---------+------------+---------------+----------------+---------------------------+
| Tên Vùng | CP Gốc  | CP Nhận Về | CP Đã Chuyển  | CP Sau Phân Bổ | Giải Thích Phân Bổ        |
+----------+---------+------------+---------------+----------------+---------------------------+
| Region A | 15000   | 127500     |               | 142500         | Nhận từ: C (50%), E (70%) |
| Region B | 15000   | 75000      |               | 90000          | Nhận từ: C (50%)          |
| Region D | 15000   | 22500      |               | 37500          | Nhận từ: E (30%)          |
+----------+---------+------------+---------------+----------------+---------------------------+

Giải thích:

• Region A: Chi phí gốc 15,000. Nhận về 127,500 (75,000 từ C + 52,500 từ E). Tổng sau phân bổ = 15,000 + 127,500 = 142,500.
• Region B: Chi phí gốc 15,000. Nhận về 75,000 (từ C). Tổng sau phân bổ = 15,000 + 75,000 = 90,000.
• Region D: Chi phí gốc 15,000. Nhận về 22,500 (từ E). Tổng sau phân bổ = 15,000 + 22,500 = 37,500.
• Các vùng nguồn (C & E): Đã chuyển hết 100% chi phí cho các vùng đích được chọn, nên không còn hiển thị.
• Tổng cộng: 142,500 + 90,000 + 37,500 = 270,000.

Ví dụ 2:

Tình huống: Người dùng chỉ chọn Region A.

Output:

+-------------------+---------+------------+---------------+----------------+----------------------------------------------------+
| Tên Vùng          | CP Gốc  | CP Nhận Về | CP Đã Chuyển  | CP Sau Phân Bổ | Giải Thích Phân Bổ                                 |
+-------------------+---------+------------+---------------+----------------+----------------------------------------------------+
| Region A          | 15000   | 127500     |               | 142500         | Nhận từ: C (50%), E (70%)                          |
| Region C (Source) | 150000  | 0          | 75000         | 75000          | Phân bổ cho: A (50% - Đã nhận), B (50% - Chưa nhận)|
| Region E (Source) | 75000   | 0          | 52500         | 22500          | Phân bổ cho: D (30% - Chưa nhận), A (70% - Đã nhận)|
+-------------------+---------+------------+---------------+----------------+----------------------------------------------------+

Giải thích:

• Region A: Được chọn nên nhận đủ tiền. Gốc 15,000 + Nhận về 127,500 (75,000 từ C + 52,500 từ E) = 142,500.
• Region C (Source): Gốc 150,000. Đã chuyển 75,000 cho A. Còn lại 75,000 (do B không được chọn nên phần tiền này được giữ lại). Sau phân bổ = 75,000.
• Region E (Source): Gốc 75,000. Đã chuyển 52,500 cho A. Còn lại 22,500 (do D không được chọn nên phần tiền này được giữ lại). Sau phân bổ = 22,500.
• Tổng hiển thị: 142,500 (A) + 75,000 (C dư) + 22,500 (E dư) = 240,000.
• Lưu ý: Tổng hệ thống là 270,000. Phần thiếu 30,000 chính là chi phí gốc của Region B (15,000) và Region D (15,000) đang bị ẩn do không được chọn.

Allocated In = 
VAR CurrentRegionID = SELECTEDVALUE ( DimRegion[RegionID] ) 
VAR IsInSlicer = CurrentRegionID IN VALUES ( SlicerRegion[RegionID] ) 

RETURN
    IF (
        IsInSlicer && NOT ISBLANK ( CurrentRegionID ),
        VAR RelevantRules = 
            CALCULATETABLE (
                AllocationRules,
                AllocationRules[TargetRegionID] = CurrentRegionID,
                REMOVEFILTERS ( AllocationRules )
            )
        VAR SourceIDs = 
            SELECTCOLUMNS ( RelevantRules, "SourceID", AllocationRules[SourceRegionID] )
        VAR SourceExpenses = 
            CALCULATETABLE (
                ADDCOLUMNS (
                    VALUES ( DimRegion[RegionID] ),
                    "TotalAmount", [Total Expenses]
                ),
                TREATAS ( SourceIDs, DimRegion[RegionID] ), 
                REMOVEFILTERS ( DimRegion ) 
            )
 
        VAR Result = 
            SUMX (
                RelevantRules,
                VAR CurrentSourceID = AllocationRules[SourceRegionID] 
                VAR CurrentPct = AllocationRules[Percentage] 
                VAR SourceAmount = 
                    MAXX (
                        FILTER ( SourceExpenses, DimRegion[RegionID] = CurrentSourceID ), -- Lookup in memory table (FE)
                        [TotalAmount]
                    )
                RETURN SourceAmount * CurrentPct
            )
        RETURN Result,
        0 -- Trả về 0 nếu không thỏa điều kiện
    )

Giải thích chi tiết

Giả sử:

• Target: Region A
• Rule 1: B -> A (50%)
• Rule 2: C -> A (30%)
• Fact Data: Region B = 1000, Region C = 2000.

Bước	Code DAX	Engine	Hành động	Kết quả (Memory)
Step 1	CALCULATETABLE(AllocationRules...)	SE	Quét bảng Rules tìm dòng có Target=A	Table: { (B->A, 50%), (C->A, 30%) }
Step 2	SELECTCOLUMNS(...)	FE	Lấy cột SourceID	List: { "B", "C" }
Step 3	CALCULATETABLE(ADDCOLUMNS(...), TREATAS(...))	SE	Gửi 1 query SQL-like xuống Fact: SELECT RegionID, SUM(Amount) WHERE RegionID IN ('B', 'C')	Cache Table: B=1000, C=2000
Step 4	SUMX(...)	FE	Lặp qua Rules và tra cứu Cache Table	Loop 1: 1000 * 0.5 = 500 Loop 2: 2000 * 0.3 = 600 Total = 1100

Context Transition

• Trong Step 3, hàm TREATAS ( SourceIDs, DimRegion[RegionID] ) đóng vai trò cực kỳ quan trọng.
• Nó lấy danh sách { "B", "C" } (đang là dữ liệu vô nghĩa) và áp đặt nó vào cột DimRegion[RegionID].
• Điều này tạo ra một Filter Context mới, ép buộc hàm [Total Expenses] chỉ tính toán cho B và C, bất chấp việc bên ngoài người dùng đang chọn Region A.

Allocated Out = 
/*
   Tính tổng số tiền mà vùng hiện tại (Source) chuyển đi cho các vùng khác (Target).
   Tính tổng % trước (SE), sau đó mới nhân tiền (FE) -> Giảm thiểu vòng lặp.
   Sử dụng tính chất phân phối: (A*x + A*y) = A * (x+y).
*/

-- 1. Context: Xác định vùng Nguồn (Source) hiện tại đang được tính toán
-- Ví dụ: Đang tính cho dòng "Region A" trong Visual Table
VAR RegionId = SELECTEDVALUE ( DimRegion[RegionID] )

-- 2. Validation: Chỉ tính nếu có vùng được chọn
VAR ShouldCalculate = NOT ISBLANK ( RegionId )

RETURN
    IF (
        NOT ShouldCalculate,
        0,
        
        -- STEP 1: CALCULATE TOTAL PERCENTAGE (Storage Engine Optimization)
        -- Source = A. Rules: A->B (50%), A->C (30%), A->D (20%)
        -- User Selection (Slicer): Chỉ chọn hiển thị Target B và C.
        -- DAX Engine: SE gửi query quét bảng AllocationRules:
        --    SELECT SUM(Percentage) 
        --    FROM AllocationRules 
        --    WHERE SourceRegionID = 'A' AND TargetRegionID IN ('B', 'C')
        -- Kết quả: 50% + 30% = 80% (0.8)
        VAR TotalPercentage = 
            CALCULATE (
                SUM ( AllocationRules[Percentage] ),
                
                -- Filter 1: Chỉ lấy các rules xuất phát từ vùng hiện tại (Source = A)
                AllocationRules[SourceRegionID] = RegionId,
                
                -- Filter 2: Chỉ tính các rules mà Target ĐANG ĐƯỢC CHỌN hiển thị
                -- TREATAS chuyển bộ lọc từ bảng SlicerRegion sang cột TargetRegionID
                TREATAS ( VALUES ( SlicerRegion[RegionID] ), AllocationRules[TargetRegionID] ),
                
                -- RemoveFilters: Đảm bảo không bị ảnh hưởng bởi các bộ lọc khác trên bảng Rules
                REMOVEFILTERS ( AllocationRules )
            )
            
        -- STEP 2:
        -- Lấy tổng chi phí gốc của vùng A. Ví dụ: 1000$
        VAR SourceAmount = [Total Expenses]
        
        -- STEP 3: FINAL CALCULATION (Formula Engine)
        -- Thay vì lặp 2 lần: (1000*0.5) + (1000*0.3) = 500 + 300 = 800
        -- Làm 1 phép nhân: 1000 * 0.8 = 800
        -- Tốc độ cực nhanh.
        RETURN SourceAmount * TotalPercentage
    )

Giải thích chi tiết

Giả sử:

• Source: Region A (Total Expenses = 1000$)
• Rules:
  • A -> B (50%)
  • A -> C (30%)
  • A -> D (20%)
• Slicer Selection: Chỉ chọn hiển thị B và C.

Bước	Code DAX	Engine	Hành động	Kết quả (Memory)
Step 1	CALCULATE(SUM(Pct), ...)	SE	Quét bảng Rules. Filter: Source=A AND Target IN {B, C}. Sum cột Percentage.	Value: 0.8 (0.5 + 0.3)
Step 2	[Total Expenses]	SE	Tính tổng chi phí cho Region A.	Value: 1000
Step 3	Result * TotalPct	FE	Thực hiện phép nhân số học đơn giản.	1000 * 0.8 = 800

Allocation Explanation = 
/*
   Tạo giải thích bằng lời văn cho dòng hiện tại (VD: "Nhận từ A, B" hoặc "Phân bổ cho C, D").
   Giúp người dùng hiểu rõ nguồn gốc/đích đến của dòng tiền mà không cần tra cứu bảng Rules.
   Sử dụng CALCULATETABLE để lấy danh sách Rules, sau đó dùng CONCATENATEX để ghép chuỗi.
*/
VAR CurrentRegionID = SELECTEDVALUE ( DimRegion[RegionID] )
VAR IsSelectedInSlicer = CurrentRegionID IN VALUES ( SlicerRegion[RegionID] )
RETURN
    IF (
        ISBLANK ( CurrentRegionID ), BLANK(),
        
        -- ---------------------------------------------------------
        -- STEP 1: XỬ LÝ INCOMING (ƯU TIÊN)
        -- Region A (đang được chọn trên Table Visual)
        -- Rules: B->A (50%), C->A (30%).
        -- ---------------------------------------------------------
        
        -- DAX Engine (SE): Quét bảng Rules tìm các dòng có Target = A.
        -- Kết quả (Memory Table): { (B, A, 50%), (C, A, 30%) }
        VAR IncomingRules = 
            CALCULATETABLE ( 
                AllocationRules, 
                AllocationRules[TargetRegionID] = CurrentRegionID,
                REMOVEFILTERS(AllocationRules) 
            )
            
        -- DAX Engine (FE): Lặp qua bảng kết quả trên để ghép chuỗi.
        -- Logic: Nếu vùng A đang được chọn trên Slicer -> Hiển thị chi tiết.
        --        Nếu không -> Báo "Không được chọn".
        VAR IncomingText = 
            IF ( 
                NOT ISEMPTY ( IncomingRules ),
                IF (
                    IsSelectedInSlicer,
                    "Nhận từ: " & 
                    CONCATENATEX ( 
                        IncomingRules, 
                        AllocationRules[SourceRegionID] & " (" & FORMAT ( AllocationRules[Percentage], "0%" ) & ")", 
                        ", " 
                    ),
                    "Không được chọn (Không nhận phân bổ)"
                )
            )
            
        -- ---------------------------------------------------------
        -- STEP 2: XỬ LÝ OUTGOING (NẾU KHÔNG PHẢI LÀ INCOMING)
        -- Lazy Evaluation: Chỉ chạy đoạn này nếu IncomingText bị RỖNG.
        -- Region B (đang được chọn trên Table Visual)
        -- Rules: B->A (50%), B->D (20%).
        -- ---------------------------------------------------------
        
        VAR OutgoingText = 
            IF (
                ISBLANK ( IncomingText ), 
                
                -- DAX Engine (SE): Quét bảng Rules tìm các dòng có Source = B.
                -- Kết quả: { (B, A, 50%), (B, D, 20%) }
                VAR OutgoingRules = 
                    CALCULATETABLE ( 
                        AllocationRules, 
                        AllocationRules[SourceRegionID] = CurrentRegionID,
                        REMOVEFILTERS(AllocationRules) 
                    )
                RETURN
                    IF (
                        NOT ISEMPTY ( OutgoingRules ),
                        "Phân bổ cho: " & 
                        CONCATENATEX ( 
                            OutgoingRules, 
                            VAR TargetID = AllocationRules[TargetRegionID]
                            -- Context Transition: Kiểm tra xem Target (A hoặc D) có đang được chọn trên Slicer không
                            VAR _Status = IF ( TargetID IN VALUES ( SlicerRegion[RegionID] ), "Active", "Inactive" )
                            RETURN TargetID & " (" & FORMAT ( AllocationRules[Percentage], "0%" ) & " - " & _Status & ")", 
                            ", " 
                        )
                    )
            )
            
        -- ---------------------------------------------------------
        -- STEP 3: FINAL RESULT
        -- Trả về Incoming nếu có, nếu không thì trả về Outgoing, cuối cùng là mặc định.
        -- ---------------------------------------------------------
        RETURN COALESCE ( IncomingText, OutgoingText, "Không phân bổ" )
    )