Excel Power Pivot - Khái niệm cơ bản về DAX

DAX (Data Analysis eXpression)ngôn ngữ là ngôn ngữ của Power Pivot. DAX được Power Pivot sử dụng để lập mô hình dữ liệu và thuận tiện cho bạn khi sử dụng cho BI tự phục vụ. DAX dựa trên bảng dữ liệu và cột trong bảng dữ liệu. Lưu ý rằng nó không dựa trên các ô riêng lẻ trong bảng như trường hợp của các công thức và hàm trong Excel.

Bạn sẽ học hai phép tính đơn giản tồn tại trong Mô hình Dữ liệu - Cột được Tính toán và Trường Tính toán trong chương này.

Cột tính toán

Cột được tính toán là một cột trong Mô hình Dữ liệu được xác định bởi một phép tính và mở rộng nội dung của bảng dữ liệu. Nó có thể được hình dung như một cột mới trong bảng Excel được xác định bởi một công thức.

Mở rộng Mô hình Dữ liệu bằng các Cột được Tính toán

Giả sử bạn có dữ liệu bán hàng của các sản phẩm theo khu vực trong bảng dữ liệu và cũng có Danh mục sản phẩm trong Mô hình dữ liệu.

Tạo Power PivotTable với dữ liệu này.

Như bạn có thể quan sát, Power PivotTable đã tóm tắt dữ liệu bán hàng từ tất cả các khu vực. Giả sử bạn muốn biết tổng lợi nhuận được thực hiện trên mỗi sản phẩm. Bạn biết giá của mỗi sản phẩm, chi phí mà nó được bán và số lượng đơn vị đã bán.

Tuy nhiên, nếu bạn cần tính toán lợi nhuận gộp, bạn cần có thêm hai cột trong mỗi bảng dữ liệu của các khu vực - Tổng Giá Sản phẩm và Tổng Lợi nhuận. Điều này là do, PivotTable yêu cầu các cột trong bảng dữ liệu để tóm tắt kết quả.

Như bạn đã biết, Tổng Giá Sản phẩm là Giá Sản phẩm * Số Đơn vị và Tổng Lợi nhuận là Tổng Thành tiền - Tổng Giá Sản phẩm.

Bạn cần sử dụng Biểu thức DAX để thêm các Cột được Tính toán như sau:

Bấm vào tab East_Sales trong Chế độ xem Dữ liệu của cửa sổ Power Pivot để xem Bảng Dữ liệu East_Sales.
Bấm vào tab Thiết kế trên Ruy-băng.
Nhấp vào Thêm.

Cột ở bên phải có tiêu đề - Thêm Cột được tô sáng.

Gõ = [Product Price] * [No. of Units] trong thanh công thức và nhấn Enter.

Một cột mới có tiêu đề CalculatedColumn1 được chèn với các giá trị được tính theo công thức bạn đã nhập.

Bấm đúp vào tiêu đề của cột mới được tính toán.
Đổi tên tiêu đề thành TotalProductPrice.

Thêm một cột được tính toán khác cho Lợi nhuận gộp như sau:

Bấm vào tab Thiết kế trên Ruy-băng.
Nhấp vào Thêm.
Cột ở bên phải có tiêu đề - Thêm Cột được tô sáng.
Gõ = [TotalSalesAmount] − [TotaProductPrice] trong thanh công thức.
Nhấn Enter.

Một cột mới có tiêu đề CalculatedColumn1 được chèn với các giá trị được tính theo công thức bạn đã nhập.

Bấm đúp vào tiêu đề của cột mới được tính toán.
Đổi tên tiêu đề thành Lợi nhuận gộp.

Thêm các Cột được Tính toán trong North_Salesbảng dữ liệu theo cách tương tự. Tổng hợp tất cả các bước, tiến hành như sau:

Bấm vào tab Thiết kế trên Ruy-băng.
Nhấp vào Thêm. Cột ở bên phải có tiêu đề - Thêm Cột được tô sáng.
Gõ = [Product Price] * [No. of Units] trong thanh công thức và nhấn Enter.
Một cột mới có tiêu đề CalculatedColumn1 được chèn với các giá trị được tính theo công thức bạn đã nhập.
Bấm đúp vào tiêu đề của cột mới được tính toán.
Đổi tên tiêu đề thành TotalProductPrice.
Bấm vào tab Thiết kế trên Ruy-băng.
Nhấp vào Thêm. Cột ở bên phải có tiêu đề - Thêm Cột được tô sáng.
Gõ = [TotalSalesAmount] − [TotaProductPrice]trong thanh công thức và nhấn Enter. Một cột mới có tiêu đềCalculatedColumn1 được chèn với các giá trị được tính theo công thức bạn đã nhập.
Bấm đúp vào tiêu đề của cột mới được tính toán.
Đổi tên tiêu đề thành Gross Profit.

Lặp lại các bước đã cho ở trên cho bảng dữ liệu Bán hàng miền Nam và bảng dữ liệu Bán hàng miền Tây.

Bạn có các cột cần thiết để tóm tắt Lợi nhuận gộp. Bây giờ, hãy tạo Power PivotTable.

Bạn có thể tóm tắt Gross Profit điều đó trở nên khả thi với các cột được tính toán trong Power Pivot và tất cả có thể được thực hiện chỉ trong một vài bước mà không có lỗi.

Bạn cũng có thể tóm tắt vùng khôn ngoan cho các sản phẩm như dưới đây -

Trường tính toán

Giả sử bạn muốn tính toán tỷ lệ phần trăm lợi nhuận tạo ra theo sản phẩm của từng khu vực. Bạn có thể làm như vậy bằng cách thêm trường được tính toán vào Bảng dữ liệu.

Nhấp vào bên dưới cột Lợi nhuận gộp trong East_Sales bảng trong cửa sổ Power Pivot.
Kiểu EastProfit: = SUM ([Gross Profit]) / sum ([TotalSalesAmount]) trong thanh công thức.
Nhấn Enter.

Trường được tính toán EastProfit được chèn vào bên dưới cột Lợi nhuận gộp.

Nhấp chuột phải vào trường được tính toán - EastProfit.
Lựa chọn Format từ danh sách thả xuống.

Hộp thoại Định dạng xuất hiện.

Lựa chọn Number trong Danh mục.
Trong hộp Định dạng, chọn Tỷ lệ phần trăm và bấm OK.

Trường tính toán EastProfit được định dạng theo phần trăm.

Lặp lại các bước để chèn các trường được tính toán sau:

NorthProfit trong bảng dữ liệu North_Sales.
SouthProfit trong bảng dữ liệu South_Sales.
WestProfit trong bảng dữ liệu West_Sales.

Note - Bạn không thể xác định nhiều hơn một trường được tính toán với một tên đã cho.

Bấm vào Power PivotTable. Bạn có thể thấy rằng các trường được tính toán xuất hiện trong bảng.

Chọn các trường - EastProfit, NorthProfit, SouthProfit và WestProfit từ các bảng trong danh sách Trường PivotTable.
Sắp xếp các trường sao cho Lợi nhuận gộp và Phần trăm lợi nhuận xuất hiện cùng nhau. Power PivotTable trông như sau:

Note - Sự Calculate Fields đã được gọi Measures trong các phiên bản Excel trước.