[DAX Tutorial] Cách sử dụng X FUNCTION trong Excel

X Function trong Excel là gì?

X Function trong Excel (thường gọi là hàm đuôi X hoặc các hàm lặp – Iterator Functions) là nhóm hàm DAX chuyên biệt trong Power Pivot và Power BI. Khác với các hàm thông thường chỉ tác động lên cả một cột dữ liệu, các hàm X Function hoạt động theo cơ chế duyệt qua từng dòng của một bảng được chỉ định, thực hiện biểu thức tính toán trên từng dòng đó, sau đó mới tổng hợp kết quả lại (tính tổng, trung bình, xếp hạng, …).

Khi nào bạn cần dùng X Function trong Excel?

• Cần thực hiện các phép toán phức tạp kết hợp nhiều cột với nhau trên từng dòng trước khi gộp kết quả (ví dụ: Số lượng nhân Đơn giá trừ đi Chiết khấu của từng dòng rồi mới cộng tổng doanh thu).
• Muốn giữ cho mô hình dữ liệu gọn nhẹ, tránh việc tạo thêm quá nhiều cột tính toán phụ (Calculated Columns) làm nặng file Excel.
• Cần tính toán động dựa trên các bộ lọc (Filter Context).

Chi tiết cú pháp và cách dùng các hàm X Function trong Excel

Hàm SUMX

Hàm SUMX sẽ lặp qua từng hàng của một bảng, thực hiện biểu thức tính toán cho hàng đó và cuối cùng cộng tất cả các kết quả lại với nhau.

• Cú pháp: SUMX(Bảng, Biểu_thức)
• Trong đó:
  • Bảng (Table): Bảng chứa các dòng dữ liệu bạn muốn lặp qua để tính toán.
  • Biểu_thức (Expression): Công thức toán học áp dụng cho từng dòng của bảng (ví dụ: nhân cột A với cột B).

Dùng SUMX khi cần tính tổng doanh thu thực tế, tổng chi phí, hoặc tổng lợi nhuận dựa trên các phép tính nhân/chia/cộng/trừ giữa các cột ở cấp độ từng giao dịch đơn lẻ.

Hàm AVERAGEX

Tương tự như SUMX, hàm AVERAGEX tính toán biểu thức cho từng dòng của bảng, sau đó tính trung bình cộng của tất cả các kết quả đó.

• Cú pháp: AVERAGEX(Bảng, Biểu_thức)

Dùng AVERAGEX khi bạn cần tính giá trị trung bình của một biểu thức phức tạp được thực hiện theo từng dòng, ví dụ như tính doanh thu trung bình thực tế thu được trên mỗi dòng chi tiết đơn hàng (sau khi đã trừ chiết khấu), thay vì chỉ tính trung bình đơn giản của một cột đơn giá đơn thuần.

Hàm RANKX

Hàm RANKX được sử dụng để xếp hạng một danh sách các giá trị dựa trên một Measure hoặc biểu thức cụ thể.

• Cú pháp cơ bản: RANKX(Bảng, Biểu_thức, [Giá_trị], [Thứ_tự], [Cách_xử_lý_trùng])
• Trong đó:
  • Bảng: Tập hợp dữ liệu muốn xếp hạng (thường kết hợp với hàm ALL để xếp hạng trên toàn bộ danh mục).
  • Biểu_thức: biểu thức tính toán giá trị dùng làm căn cứ để xếp hạng.
  • Thứ_tự (Order): Chọn 0 hoặc DESC để xếp hạng từ cao xuống thấp (mặc định); chọn 1 hoặc ASC để xếp hạng từ thấp lên cao.

Rất hữu ích khi cần xếp hạng nhân viên sales xuất sắc nhất, sản phẩm bán chạy nhất, hoặc chi nhánh có doanh thu cao nhất trong báo cáo tài chính.

So sánh hàm thông thường (Non-X) và hàm lặp (X Function)

Để tránh nhầm lẫn khi xây dựng báo cáo trong Power Pivot, dưới đây là bảng so sánh trực quan giúp bạn phân biệt rõ bản chất của hai nhóm hàm này:

Minh họa thực tế các hàm X Function trong Excel

Ví dụ 1: Tính tổng doanh thu bằng hàm SUMX

Trong thực tế, doanh thu của mỗi dòng sản phẩm bán ra được tính bằng: Số lượng nhân Đơn giá trừ đi Số tiền chiết khấu. Nếu sử dụng hàm SUM thông thường, bạn sẽ bế tắc vì không thể viết trực tiếp phép toán này trong hàm.

Để giải quyết, chúng ta tạo một Measure có tên là Total Revenue 2 nằm trong bảng dữ liệu:

• Công thức: = SUMX(OrderLine, OrderLine[Qty] * OrderLine[UnitPrice] – OrderLine[LineDiscount$])

Mô tả cơ chế hoạt động:

• Hàm SUMX sẽ truy cập vào bảng dữ liệu OrderLine.
• Trên từng dòng (hàng) của bảng OrderLine, hệ thống lấy số lượng hàng bán ra OrderLine[Qty] nhân với đơn giá sản phẩm OrderLine[UnitPrice], sau đó trừ đi số tiền chiết khấu trực tiếp trên dòng đó OrderLine[LineDiscount$].
• Sau khi tính xong giá trị doanh thu thực tế cho tất cả các dòng, SUMX cộng dồn tất cả kết quả lại để cho ra tổng doanh thu cuối cùng.

Kết quả thực tế: Khi đưa Measure Total Revenue 2 vào Pivot Table, kết quả trả về là 44.837.769. Con số này hoàn toàn chính xác và khớp 100% với thước đo doanh thu tổng ban đầu, chứng minh hiệu quả tuyệt đối của hàm lặp SUMX mà không cần tạo thêm bất kỳ một cột phụ nào trong bảng dữ liệu thô.

Ví dụ 2: Tính doanh thu trung bình bằng AVERAGEX

Khi cần xác định mức doanh thu trung bình thực tế thu về trên mỗi dòng sản phẩm (sau khi đã áp dụng các khoản chiết khấu trực tiếp trên dòng đó), hàm AVERAGEX chính là công cụ hỗ trợ chính xác nhất.

Chúng ta tiến hành tạo Measure có tên là AVG Revenue với cấu trúc tương tự:

• Công thức: = AVERAGEX(OrderLine, OrderLine[Qty] * OrderLine[UnitPrice] – [LineDiscount$])

Mô tả cơ chế hoạt động:

• Hàm AVERAGEX lặp qua từng hàng trong bảng OrderLine.
• Tính toán doanh thu thực tế sau chiết khấu của dòng đó: Số lượng * Đơn giá – Chiết khấu.
• Cộng tất cả doanh thu của các dòng lại rồi chia cho tổng số lượng dòng có trong bảng để tìm ra giá trị trung bình cộng.

Kết quả thực tế: Trên bảng Pivot Table, giá trị của Measure AVG Revenue hiển thị là 246 (đã được định dạng làm tròn thành số nguyên – Whole Number). Chỉ số này giúp bộ phận vận hành đánh giá được quy mô trung bình của từng hạng mục hàng hóa xuất kho.

Ví dụ 3: Xếp hạng danh mục sản phẩm theo doanh thu bằng hàm RANKX

Một trong những ứng dụng kinh điển nhất của X Function là khả năng tự động xếp hạng thứ tự danh mục sản phẩm từ bán chạy nhất đến bán chạy kém nhất.

Chúng ta tạo Measure có tên là Rank Category by Revenue để thực hiện yêu cầu này:

• Công thức: = RANKX(ALL(‘Product'[Category]), [Total Revenue])

Mô tả cơ chế hoạt động:

• Hàm ALL(‘Product'[Category]) có nhiệm vụ loại bỏ mọi bộ lọc đang áp dụng lên cột danh mục sản phẩm. Điều này cực kỳ quan trọng, giúp hàm RANKX có thể nhìn thấy toàn bộ danh sách các danh mục để so sánh chéo với nhau. Nếu không có ALL, mỗi dòng sản phẩm trên Pivot Table sẽ tự so sánh với chính nó và đều trả về hạng 1.
• Hàm RANKX sẽ tính toán thước đo [Total Revenue] cho từng danh mục sản phẩm, sau đó tiến hành so sánh và xếp thứ tự từ cao xuống thấp (DESC).

Kết quả thực tế: Khi kéo danh mục sản phẩm (Category) vào phần Row và Measure xếp hạng vào phần Values của Pivot Table, chúng ta thu được bảng báo cáo trực quan:

• Hạng 1: Grocery (Doanh thu cao nhất).
• Hạng 2: Jewelry.
• Hạng 3: Toys.
• Các ngành hàng khác như Footwear (Hạng 5), Apparel (Hạng 8), Books (Hạng 10 – doanh thu thấp nhất trong nhóm).
• Phần Grand Total hiển thị hạng 1 (xếp hạng tổng thể).

Lưu ý khi sử dụng X Function trong Excel

Các hàm lặp X Function trong Excel vô cùng mạnh mẽ, nhưng do cơ chế tính toán quét qua từng dòng nên chúng dễ trở thành nguyên nhân chính khiến file Excel bị đơ, giật lag hoặc báo lỗi thiếu bộ nhớ. Dưới đây là những kinh nghiệm thực tế xương máu được chia sẻ từ các chuyên gia phân tích dữ liệu hàng đầu giúp bạn tối ưu hóa hiệu suất tối đa:

Tránh sử dụng trực tiếp trên các bảng dữ liệu thô quá lớn

• Công cụ lưu trữ của Excel Power Pivot được thiết kế để nén dữ liệu theo cột cực kỳ tối ưu. Tuy nhiên, khi bạn sử dụng một hàm X Function trực tiếp lên một bảng dữ liệu giao dịch khổng lồ (vài triệu đến hàng chục triệu dòng), bạn đang ép công cụ này phải giải nén và duyệt qua từng dòng một. Điều này phá vỡ lợi thế nén dữ liệu của Power Pivot và tiêu tốn một lượng RAM khổng lồ của máy tính.
• Giải pháp tối ưu:
  • Nếu bắt buộc phải tính toán lặp trên bảng lớn, hãy cố gắng lọc bớt bảng dữ liệu trước bằng cách lồng hàm lặp bên trong hàm CALCULATE hoặc sử dụng hàm FILTER để chỉ lặp qua những dòng dữ liệu thực sự cần thiết.
  • Ưu tiên thực hiện phép tính lặp trên các bảng Dim thay vì lặp trên bảng Fact nếu logic bài toán cho phép.

Ưu tiên sử dụng Measure đã có sẵn

• Khi viết biểu thức tính toán trong hàm lặp, bạn có hai lựa chọn: viết công thức thô trực tiếp (ví dụ: SUMX(Table, Table[Qty] * Table[Price])) hoặc gọi một Measure đã được định nghĩa từ trước (ví dụ: SUMX(Table, [Total Sales])).
• Tại sao nên gọi Measure có sẵn?
  • Sự chuyển đổi ngữ cảnh: Khi bạn gọi một Measure bên trong một hàm lặp, DAX sẽ tự động bọc Measure đó trong một hàm CALCULATE ngầm định. Cơ chế này lập tức chuyển đổi ngữ cảnh dòng (Row Context) hiện tại thành ngữ cảnh bộ lọc (Filter Context), giúp công thức tính toán chính xác và thông minh hơn khi Pivot Table có nhiều chiều phân tích.
  • Tốc độ xử lý: Các Measure được định nghĩa trước thường được lưu trữ sẵn trong bộ nhớ đệm. Việc gọi lại Measure giúp tái sử dụng kết quả tính toán có sẵn, giảm đáng kể thời gian CPU phải xử lý lại từ đầu so với việc bạn viết lại một biểu thức toán học thô sơ.

Kết hợp với các hàm loại bỏ bộ lọc (ALL, ALLSELECTED) khi dùng RANKX

• Tại sao RANKX dễ bị lỗi hiển thị? Khi bạn kéo một Measure chứa hàm RANKX vào Pivot Table, Power Pivot sẽ tính toán thứ hạng cho từng dòng hiển thị trên bảng. Nếu bạn không sử dụng hàm loại bỏ bộ lọc, ngữ cảnh bộ lọc của dòng hiện tại (ví dụ: chỉ lọc riêng ngành hàng “Footwear”) sẽ khiến RANKX chỉ nhìn thấy đúng một ngành hàng đó, kết quả là tất cả các dòng đều đồng loạt trả về xếp hạng 1.
• Mẹo áp dụng từ thực tế:
  • Dùng hàm ALL: Sử dụng ALL(Table[Column]) khi bạn muốn xếp hạng cố định dựa trên toàn bộ danh mục sản phẩm, hoàn toàn bỏ qua bất kỳ bộ lọc nào đang áp dụng lên cột đó trên Pivot Table.
  • Dùng hàm ALLSELECTED: Đây là giải pháp chuyên nghiệp hơn khi làm báo cáo động. Hàm ALLSELECTED giúp loại bỏ bộ lọc trên dòng của Pivot Table nhưng vẫn giữ lại bộ lọc từ các thanh kéo (Slicers) hoặc bộ lọc tổng của trang tính. Điều này giúp bảng xếp hạng tự động cập nhật chuẩn xác theo phạm vi dữ liệu mà người xem báo cáo đang chủ động lựa chọn.

Tổng kết

Làm chủ các X Function trong Excel là bước đệm tối quan trọng giúp bạn nâng tầm tư duy xử lý dữ liệu của mình. Nhờ khả năng can thiệp trực tiếp vào từng dòng giao dịch mà vẫn giải quyết được các bài toán tổng hợp phức tạp, những hàm như SUMX, AVERAGEX và RANKX chính là trợ thủ đắc lực giúp bạn xây dựng hệ thống báo cáo quản trị động chuyên nghiệp và chuẩn xác.