[DAX Tutorial] Cách sử dụng hàm Calculate trong Excel

Nếu bạn đang làm việc với dữ liệu lớn trong Excel thông qua công cụ Power Pivot, việc làm quen với ngôn ngữ DAX là điều bắt buộc. Trong số hàng trăm hàm DAX, hàm CALCULATE trong Excel được xem là vua của mọi phép tính. Hàm này cho phép bạn thay đổi ngữ cảnh lọc (filter context) một cách linh hoạt để tạo ra các báo cáo động cực kỳ mạnh mẽ. Trong bài viết này, Starttrain sẽ giúp bạn hiểu rõ bản chất, cấu trúc cú pháp và cách ứng dụng hàm CALCULATE trong Excel qua các ví dụ thực tế dựa trên bộ dữ liệu bán hàng.

Hàm CALCULATE trong Excel là gì?

Hàm CALCULATE trong Excel là một hàm DAX dùng trong Power Pivot và Power BI, có chức năng đánh giá một biểu thức (expression) trong một ngữ cảnh lọc đã được sửa đổi hoặc áp dụng các bộ lọc mới.

Nói một cách đơn giản, nếu bạn muốn tính toán một chỉ số (như doanh thu, số lượng bán, số khách hàng) nhưng chỉ áp dụng cho một nhóm điều kiện đặc thù (như khu vực miền Bắc, ngành hàng Thiết bị gia dụng, hoặc tháng trước đó) thì CALCULATE chính là công cụ giúp bạn thực hiện điều này.

Cú pháp của hàm CALCULATE trong Excel

Cú pháp chuẩn của hàm CALCULATE như sau:

CALCULATE(Expression, Filter1, Filter2, ...)

Trong đó:

Expression (Biểu thức): Tham số bắt buộc. Đây có thể là một Measure đã được tính trước đó (ví dụ: [Total Revenue], [Total Qty]) hoặc một hàm tổng hợp (ví dụ: SUM, AVERAGE, COUNT) thực hiện trên một cột cụ thể.
Filter1, Filter2, … (Các bộ lọc): Tham số tùy chọn. Đây là các điều kiện lọc (dưới dạng biểu thức logic hoặc một bảng dữ liệu) dùng để giới hạn lại dữ liệu trước khi biểu thức được tính toán. Bạn có thể thêm nhiều bộ lọc cùng lúc, các bộ lọc này sẽ được kết hợp với nhau bằng điều kiện AND (đồng thời xảy ra).

Khi nào nên sử dụng hàm CALCULATE?

Bạn cần sử dụng hàm CALCULATE trong Excel khi:

Muốn ghi đè lên bộ lọc hiện tại của PivotTable: Ví dụ, PivotTable đang lọc theo tất cả sản phẩm, nhưng bạn muốn tạo một cột chỉ hiển thị doanh số của một nhóm sản phẩm nhất định.
Thực hiện các phép tính thời gian (Time Intelligence): Như so sánh doanh thu tháng này với tháng trước, tính lũy kế từ đầu năm đến nay (YTD), hoặc tăng trưởng cùng kỳ năm ngoái (YoY).
Tính tỷ trọng đóng góp: Để biết doanh số của sản phẩm A chiếm bao nhiêu % tổng doanh số, bạn cần CALCULATE để tính tổng doanh số chung mà không bị ảnh hưởng bởi bộ lọc dòng của sản phẩm A.

Xem thêm: Power BI Calculated Column là gì? Có gì khác với Measure

Minh họa cách sử dụng hàm CALCULATE trong Excel

Để hình dung trực quan nhất, hãy cùng thực hành tạo các Measure bằng hàm CALCULATE trong Excel dựa trên bộ dữ liệu thực tế qua các bước sau đây.

Bước chuẩn bị: Tạo Measure mới trong Power Pivot

Để bắt đầu viết công thức DAX, bạn thực hiện các thao tác:

Trên thanh công cụ của Excel, chọn thẻ Power Pivot.
Nhấp chọn vào mục Measures, sau đó chọn New Measure… để mở hộp thoại thiết lập công thức.

Trước khi tạo Measure bằng DAX, người dùng cần đưa dữ liệu vào Data Model trong Excel và thiết lập các mối quan hệ giữa các bảng.

Ví dụ 1: Tính doanh thu của riêng loại sản phẩm Home

Giả sử bạn đã có sẵn Measure tên là [Total Revenue] để tính tổng doanh thu toàn bộ hệ thống. Bây giờ, bạn muốn tính riêng doanh thu cho nhóm ngành hàng Home (Đồ gia dụng).

Tại hộp thoại Measure, bạn thiết lập các thông số sau:

Measure name (Tên chỉ số): Nhập Total Home Revenue.
Formula (Công thức): Bạn nhập công thức sau vào ô trống: =CALCULATE([Total Revenue], ‘Product'[Category] = “Home”)
Formatting Options (Định dạng): Chọn loại dữ liệu là Number, định dạng Decimal Number, số thập phân để là 0 và tích chọn Use 1000 separator (,) để hiển thị dấu phân tách hàng nghìn cho dễ đọc.

Sau khi thiết lập xong, bạn nhấn OK.

Kết quả hiển thị trên PivotTable:

Khi bạn kéo Measure Total Home Revenue vừa tạo vào vùng Values của PivotTable, Excel sẽ lập tức trả về kết quả chính xác cho doanh thu nhóm hàng Home là 4,306,134 (Trong khi tổng doanh thu chung Total Revenue của toàn bộ hệ thống là 44,837,769). Phép tính này hoàn toàn tự động loại bỏ các danh mục sản phẩm khác để chỉ giữ lại giá trị của sản phẩm “Home”.

Ví dụ 2: Tính doanh thu của cùng kỳ tháng trước

Đây là một ứng dụng rất phổ biến trong phân tích báo cáo tài chính và bán hàng. Để tính doanh thu tháng trước nhằm mục đích so sánh tăng trưởng, chúng ta sẽ lồng thêm hàm dịch chuyển thời gian DATEADD vào bên trong bộ lọc của hàm CALCULATE.

Trong hộp thoại Measure mới, bạn thiết lập:

Measure name: Total Revenue Last Month
Formula (Công thức): =CALCULATE([Total Revenue], DATEADD(‘Calendar'[Date], -1, MONTH))
Formatting Options: Định dạng là Number -> Decimal Number, làm tròn 0 chữ số thập phân, sử dụng dấu ngăn cách hàng nghìn.

Giải thích công thức: Hàm DATEADD(‘Calendar'[Date], -1, MONTH) có vai trò lùi ngữ cảnh thời gian hiện tại về trước đó đúng 1 tháng. Khi lồng vào CALCULATE, Excel sẽ tính toán [Total Revenue] dựa trên khoảng thời gian đã được dịch lùi này.Kiểm chứng độ chính xác của kết quả:

Khi kéo Measure Total Revenue Last Month vào PivotTable hiển thị theo các tháng (FiscalMonth), ta có bảng kết quả so sánh đối chiếu rõ ràng như sau:

Tại dòng dòng tháng FY25-12: Doanh thu thực tế (Total Revenue) là 509,542. Doanh thu tháng trước (Total Revenue Last Month) hiển thị giá trị trống hoặc của tháng trước nữa.
Tại dòng tháng FY26-01:
- Doanh thu thực tế (Total Revenue) đạt 453,799.
- Doanh thu tháng trước (Total Revenue Last Month) ghi nhận con số 2,836,442 (Chính xác bằng doanh thu thực tế của tháng liền kề trước đó là tháng FY25-12).

Nhờ vào sự kết hợp giữa CALCULATE và DATEADD, bạn dễ dàng thực hiện so sánh doanh số theo tháng (Month-on-Month) mà không cần phải viết các hàm IF phức tạp hay cắt dán thủ công dữ liệu Excel.

Ứng dụng của hàm Calculate trong Excel trong thực tế

Báo cáo phân tích tỷ trọng đóng góp (Market Share)

Bạn cần tính xem doanh thu của nhóm hàng “Home” chiếm bao nhiêu phần trăm trong tổng doanh thu toàn doanh nghiệp. Nếu chỉ lấy doanh thu của Home chia cho tổng doanh thu trên từng dòng của PivotTable thông thường, kết quả sẽ luôn ra 100% vì bộ lọc của dòng đang ép dữ liệu chỉ tính cho “Home”.
Giải pháp với CALCULATE: Chúng ta sử dụng CALCULATE kết hợp với hàm ALL để xóa bỏ bộ lọc hiện tại trên bảng sản phẩm. Hàm ALL sẽ yêu cầu Excel tính toán doanh thu trên toàn bộ danh mục sản phẩm, bất kể dòng đó đang hiển thị sản phẩm nào.
Công thức DAX: Home Revenue Share = DIVIDE([Total Home Revenue], CALCULATE([Total Revenue], ALL(‘Product’)))
Giúp nhà quản trị đánh giá ngay lập tức mức độ đóng góp của từng dòng sản phẩm vào dòng tiền chung để đưa ra chiến lược đầu tư hoặc cắt giảm hợp lý.

Nếu bạn muốn học bài bản cách sử dụng DAX, xây dựng Data Model và tạo dashboard phân tích dữ liệu thực tế, hãy tham khảo khóa học phân tích dữ liệu bằng Excel hoặc khóa học Power BI tại Starttrain.

Tính toán tốc độ tăng trưởng định kỳ (Growth Rate – MoM, YoY)

Sau khi đã tính được doanh thu của tháng trước (ở Ví dụ 2), mục tiêu cuối cùng của bạn là tính ra phần trăm tăng trưởng doanh thu tháng này so với tháng trước (Month-on-Month Growth Rate).
Giải pháp với CALCULATE: Sử dụng kết quả dịch chuyển thời gian của CALCULATE làm mẫu số, sau đó áp dụng công thức tính tỷ lệ tăng trưởng chuẩn thông qua hàm DIVIDE để tránh lỗi chia cho 0.
Công thức DAX: Revenue MoM Growth % = DIVIDE([Total Revenue] – [Total Revenue Last Month], [Total Revenue Last Month])
Chỉ số này giúp bộ phận kinh doanh đo lường chính xác tốc độ tăng trưởng doanh số theo tháng. Tương tự, bạn có thể thay thế MONTH bằng YEAR trong hàm DATEADD để tính tốc độ tăng trưởng cùng kỳ năm trước (Year-over-Year).

Báo cáo lũy kế cộng dồn (YTD – Year To Date)

Kế toán và ban giám đốc luôn cần theo dõi doanh số cộng dồn từ ngày 1 tháng 1 của năm tài chính hiện tại cho đến ngày báo cáo để kiểm tra tiến độ hoàn thành chỉ tiêu doanh thu năm.
Giải pháp với CALCULATE: Kết hợp hàm CALCULATE với các hàm lọc thời gian đặc thù như DATESYTD. Hàm này sẽ tự động tạo ra một dải bộ lọc ngày kéo dài từ đầu năm đến ngày hiện tại của bộ lọc.
Công thức DAX: YTD Revenue = CALCULATE([Total Revenue], DATESYTD(‘Calendar'[Date]))
Vẽ ra đường biểu đồ doanh thu đi lên liên tục theo thời gian, giúp doanh nghiệp so sánh trực quan tiến độ thực tế so với mục tiêu đề ra (KPI target) của cả năm.

Báo cáo lọc nhiều điều kiện logic phức tạp

Bạn cần tính doanh thu của những đơn hàng vừa thuộc danh mục “Home”, vừa được vận chuyển bằng phương thức “Express” (Điều kiện đồng thời – AND). Hoặc tính doanh thu của những đơn hàng hoặc là “Home” hoặc là “Electronics” (Điều kiện lựa chọn – OR).
Giải pháp với CALCULATE:
- Với điều kiện AND: Bạn chỉ cần truyền các điều kiện lọc vào các tham số lọc tiếp theo của CALCULATE, phân tách bằng dấu phẩy. =CALCULATE([Total Revenue], ‘Product'[Category] = “Home”, ‘Shipping'[Method] = “Express”)
- Với điều kiện OR: Bạn sử dụng toán tử hai dấu gạch đứng || ngay trong một tham số lọc. =CALCULATE([Total Revenue], ‘Product'[Category] = “Home” || ‘Product'[Category] = “Electronics”)
Hỗ trợ thực hiện các phân tích sâu (Deep-dive) vào các phân khúc khách hàng hoặc sản phẩm ngách cực kỳ nhanh chóng mà không cần tạo thêm các cột phụ làm nặng file Excel.

Tổng kết

Hàm CALCULATE trong Excel không chỉ đơn thuần là một công cụ tính toán, mà là chìa khóa mở ra sức mạnh phân tích dữ liệu đa chiều trong Power Pivot và Power BI. Khả năng can thiệp và ghi đè linh hoạt lên ngữ cảnh lọc (Filter Context) giúp CALCULATE trở thành một hàm không thể thay thế khi bạn muốn xây dựng các mô hình dữ liệu động, tự động hóa các chỉ số phức tạp như lũy kế, tăng trưởng, và phân tách dữ liệu đa điều kiện.

283