Hàm DAX trong Excel được viết trong Data Model qua Power Pivot để tạo cột tính toán và measure dùng trong PivotTable. Bạn không nhập DAX trực tiếp vào ô như SUMIF; hãy đưa dữ liệu vào mô hình, tạo measure rồi phân tích theo cửa hàng, sản phẩm hoặc thời gian.
Bài này dùng một bảng bán hàng giả lập để bạn thực hành doanh thu, lợi nhuận và biên lợi nhuận, đồng thời kiểm tra vì sao dòng tổng có thể khác cách cộng thông thường.
DAX trong Excel dùng ở đâu?
DAX làm việc với bảng và cột trong mô hình dữ liệu. Công thức thường tham chiếu BanHang[SoLuong] thay vì địa chỉ C2:C100. Khi bạn lọc PivotTable theo cửa hàng, measure tính lại trên phần dữ liệu thuộc bộ lọc đó.
Power Query phụ trách nhập và làm sạch dữ liệu bằng ngôn ngữ M; Power Pivot phụ trách mô hình và phép tính DAX. Nếu chỉ cần cộng một vùng trên worksheet, công thức Excel vẫn thuận tiện. Nếu cần nhiều chỉ tiêu dùng chung qua các PivotTable, measure giúp giữ một định nghĩa thống nhất.
Bạn có thể xem thêm các nhóm hàm DAX cơ bản sau khi hoàn thành ví dụ. Phần dưới tập trung vào cách thao tác trong Excel, không yêu cầu cài Power BI.
Chuẩn bị Power Pivot và bảng bán hàng
Bật Power Pivot
Hướng dẫn áp dụng cho Excel desktop trên Windows có hỗ trợ Power Pivot. Vào File → Options → Add-ins, chọn COM Add-ins tại Manage rồi bấm Go. Đánh dấu Microsoft Power Pivot for Excel và xác nhận. Nếu không có mục này, kiểm tra phiên bản và quyền sử dụng Office; không tải add-in từ website không rõ nguồn.
Tạo bảng dữ liệu đầu vào
Nhập bảng sau vào worksheet, chọn vùng rồi nhấn Ctrl+T, xác nhận có hàng tiêu đề. Trong Table Design, đặt tên bảng là BanHang. Các khoản tiền là đồng Việt Nam; nhập số nguyên, không gõ thêm ký hiệu tiền vào ô.
| CuaHang | SanPham | SoLuong | DonGia | GiaVon |
|---|---|---|---|---|
| Hà Nội | Cà phê | 10 | 30000 | 18000 |
| Hà Nội | Trà | 5 | 20000 | 12000 |
| Đà Nẵng | Cà phê | 8 | 30000 | 18000 |
| Đà Nẵng | Bánh | 4 | 25000 | 20000 |
Mỗi dòng đại diện một mặt hàng bán tại một cửa hàng trong ví dụ. GiaVon là giá vốn mỗi đơn vị, chưa phải tổng giá vốn của dòng. Đây là chỗ cần chốt trước khi viết phép nhân; nếu nguồn đã có giá vốn cả dòng, nhân tiếp số lượng sẽ làm sai kết quả.
Chọn một ô trong bảng rồi vào Power Pivot → Add to Data Model. Kiểm tra SoLuong, DonGia và GiaVon đều là kiểu số trong cửa sổ Power Pivot. Nếu số đang là văn bản, sửa dữ liệu nguồn và cập nhật mô hình trước khi tiếp tục.
Tạo measure DAX đầu tiên trong Excel
Trong cửa sổ Excel, chọn Power Pivot → Measures → New Measure. Chọn bảng BanHang, đặt Measure Name là Doanh thu. Ở Formula, nhập biểu thức dưới đây, gồm dấu bằng đầu dòng:
=SUMX(BanHang, BanHang[SoLuong] * BanHang[DonGia])Bấm Check Formula, chọn định dạng số phù hợp rồi lưu. SUMX duyệt từng dòng đang được xét, nhân số lượng với đơn giá và cộng các kết quả. Không dùng SUM(SoLuong) nhân SUM(DonGia), vì đó là tích của hai tổng, khác tổng thành tiền từng dòng.
Công thức trong bài dùng dấu phẩy phân cách đối số. Nếu thiết lập vùng trên máy yêu cầu dấu chấm phẩy, đổi dấu phân cách tương ứng. Nếu viết trong Calculation Area của Power Pivot, cú pháp đầy đủ là Doanh thu:=SUMX(...); đừng dán cả tên measure vào ô Formula của hộp New Measure.
Đưa measure vào PivotTable
Từ cửa sổ Power Pivot, chọn Home → PivotTable và tạo bảng ở worksheet mới. Kéo CuaHang vào Rows, kéo measure Doanh thu vào Values. Nếu tạo PivotTable từ Excel, hãy chọn nguồn là Data Model của workbook để nhìn thấy measure.
Kết quả cần có: Hà Nội 400.000 đồng, Đà Nẵng 340.000 đồng, tổng 740.000 đồng. Thêm SanPham vào bộ lọc và chọn Cà phê: tổng còn 540.000 đồng. Phép thử này xác nhận measure đang phản ứng với bộ lọc.
Thêm lợi nhuận và biên lợi nhuận
Tạo lần lượt ba measure dưới đây trong cùng hộp New Measure. Mỗi khối là nội dung Formula; tên measure được ghi riêng ở đầu mục.
Measure Tong gia von
=SUMX(BanHang, BanHang[SoLuong] * BanHang[GiaVon])Measure Loi nhuan gop
=[Doanh thu] - [Tong gia von]Measure Bien loi nhuan
=DIVIDE([Loi nhuan gop], [Doanh thu])Chọn định dạng Percentage cho Bien loi nhuan. DIVIDE trả về BLANK khi mẫu số bằng 0 nếu không chỉ định kết quả thay thế. Giữ ô trống thường giúp phân biệt trường hợp chưa phát sinh doanh thu với hoạt động có biên lợi nhuận thật sự bằng 0%.
Trong ví dụ, tổng giá vốn là 464.000 đồng và lợi nhuận gộp là 276.000 đồng. Biên lợi nhuận toàn bộ bằng 276.000 chia 740.000, xấp xỉ 37,30%. Chỉ tiêu này chưa trừ tiền thuê mặt bằng, lương hay chi phí khác, nên không gọi là lợi nhuận ròng.
Vì sao dòng tổng tỷ lệ không bằng trung bình các dòng?
Hà Nội có biên lợi nhuận 40%; Đà Nẵng khoảng 34,12%. Trung bình cộng hai tỷ lệ là khoảng 37,06%, khác 37,30% của toàn bộ dữ liệu. Doanh thu hai cửa hàng khác nhau nên mỗi tỷ lệ có trọng số khác nhau.
Measure tỷ lệ được tính lại từ tổng lợi nhuận và tổng doanh thu trong ngữ cảnh của ô Grand Total. Excel không mặc định lấy trung bình các tỷ lệ đang hiển thị. Vì vậy, hãy đối soát tử số và mẫu số trước khi kết luận dòng tổng sai.
Nếu muốn một chỉ tiêu cố định theo điều kiện, bạn có thể tạo measure Doanh thu Ha Noi bằng công thức:
=CALCULATE([Doanh thu], BanHang[CuaHang] = "Hà Nội")CALCULATE thay đổi ngữ cảnh lọc. Điều kiện trên thay bộ lọc hiện tại ở cột CuaHang, nên đặt measure này cạnh các cửa hàng có thể lặp doanh thu Hà Nội. Với báo cáo theo cửa hàng thông thường, chỉ cần measure Doanh thu cùng CuaHang ở Rows. Bài cách dùng CALCULATE trong DAX giải thích kỹ hơn cơ chế này.
Phân biệt measure và calculated column
Calculated column tính giá trị cho từng dòng và lưu trong mô hình; phù hợp khi cần một thuộc tính để kéo vào Rows, Columns hoặc bộ lọc. Measure trả về kết quả theo ngữ cảnh của PivotTable, phù hợp cho tổng tiền, số lượng và tỷ lệ.
Bạn có thể tạo cột thành tiền rồi SUM cột đó, hoặc dùng SUMX như bài này. Hai cách có thể cho cùng doanh thu nhưng khác cách lưu trữ. Với chỉ tiêu chỉ cần tổng hợp, nên bắt đầu bằng measure; đừng tạo nhiều cột trung gian chỉ để cộng lại.
Checklist trước khi dùng số liệu báo cáo
- Kiểm tra cấp độ từng dòng, loại bỏ các dòng tổng có sẵn trong dữ liệu nguồn.
- Đối chiếu doanh thu chưa lọc với tổng thành tiền tính tay từ bốn dòng mẫu.
- Lọc từng cửa hàng và sản phẩm để kiểm tra measure phản ứng đúng.
- Sửa một số lượng ở worksheet, cập nhật mô hình và Refresh PivotTable, rồi kiểm tra số mới.
- Ghi rõ giá bán có gồm thuế hay chưa, giá vốn theo đơn vị hay theo dòng.
Khi có nhiều bảng, cần kiểm tra thêm khóa và quan hệ trước khi dùng thuộc tính từ bảng khác để lọc. Đừng dùng công thức phức tạp để che lỗi dữ liệu nguồn hoặc quan hệ không phù hợp.
Câu hỏi thường gặp
Có nhập hàm DAX vào ô Excel được không?
Bạn không nhập công thức DAX trực tiếp như hàm worksheet. Hãy tạo measure hoặc calculated column trong Data Model qua Power Pivot, sau đó dùng chúng trong PivotTable.
Vì sao không thấy measure trong danh sách PivotTable?
Kiểm tra PivotTable có dùng Data Model của workbook hay chỉ dùng vùng ô thông thường. Sau đó mở rộng bảng chứa measure và kiểm tra measure đã được lưu thành công.
Có thể dùng nguyên công thức từ Power BI sang Excel không?
Nhiều hàm nền tảng dùng chung, nhưng khả năng hỗ trợ phụ thuộc phiên bản. Kiểm tra từng hàm, tên bảng, quan hệ và định dạng ngày; không mặc định mọi tính năng DAX mới đều có trong Excel.
Tại sao thêm dữ liệu mà doanh thu chưa đổi?
Kiểm tra dòng mới nằm trong Excel Table, cập nhật Data Model rồi Refresh PivotTable. Nếu còn bộ lọc đang ẩn dữ liệu mới, bỏ lọc và đối soát lại trước khi sửa measure.
Nếu bạn cần người làm giúp mô hình và báo cáo Power BI từ dữ liệu Excel, có thể xem thông tin báo giá làm Power BI trên SenData. Chuẩn bị một bảng dữ liệu đã ẩn thông tin nhạy cảm cùng danh sách chỉ tiêu sẽ giúp trao đổi yêu cầu rõ hơn.
Sẵn sàng xây dựng dashboard Power BI?
Kết nối với freelancer Power BI phù hợp, xem báo cáo nhúng ngay trên trình duyệt và tải file .pbix về sau khi thanh toán.
Đào Ngọc Tú
Cộng tác viên chuyên gia tại SenData. Chia sẻ kiến thức về phát triển Power BI, trực quan hóa dữ liệu và best practice business intelligence.
