Khi dùng các Cột được Tính và Trường được Tính toán

Khi lần đầu tìm hiểu cách sử dụng Power Pivot, hầu hết người dùng khám phá ra sức mạnh thực sự đang trong quá trình tổng hợp hoặc tính toán kết quả theo một cách nào đó. Nếu dữ liệu của bạn có một cột chứa giá trị số, bạn có thể dễ dàng tổng hợp nó bằng cách chọn nó trong PivotTable hoặc Danh sách Trường Power View. Theo tự nhiên, vì thuộc tính số, nó sẽ tự động được tính tổng, tính trung bình, đếm hoặc bất kỳ loại tổng hợp nào bạn chọn. Đây được gọi là một biện pháp ngầm. Các biện pháp ngầm là tuyệt vời để tổng hợp nhanh chóng và dễ dàng, nhưng chúng có giới hạn, và những giới hạn đó hầu như luôn có thể được khắc phục với các biện pháp rõ ràng và cột được tính toán.

Trước tiên, chúng ta hãy xem ví dụ trong đó chúng ta sử dụng cột được tính để thêm giá trị văn bản mới cho mỗi hàng trong bảng có tên Sản phẩm. Mỗi hàng trong bảng Sản phẩm chứa tất cả các loại thông tin về từng sản phẩm chúng tôi bán. Chúng tôi có các cột về Tên Sản phẩm, Màu sắc, Kích thước, Giá đại lý, v.v. Chúng tôi có một bảng liên quan khác có tên là Danh mục Sản phẩm có chứa một cột Tên_Danh_mục_Sản_phẩm. Điều chúng tôi muốn là cho mỗi sản phẩm trong bảng Sản phẩm bao gồm tên danh mục sản phẩm từ bảng Danh mục Sản phẩm. Trong bảng Sản phẩm của chúng tôi, chúng ta có thể tạo một cột được tính có tên là Danh mục Sản phẩm như sau:

Công thức Danh mục Sản phẩm mới của chúng tôi sử dụng hàm RELATED DAX để lấy giá trị từ cột ProductCategoryName trong bảng Danh mục Sản phẩm liên quan, rồi nhập các giá trị đó cho từng sản phẩm (mỗi hàng) trong bảng Sản phẩm.

Đây là một ví dụ tuyệt vời về cách chúng ta có thể sử dụng một cột được tính để thêm một giá trị cố định cho mỗi hàng mà chúng ta có thể dùng sau này trong khu vực HÀNG, CỘT hoặc BỘ LỌC của PivotTable hoặc trong báo cáo Power View.

Chúng ta hãy tạo một ví dụ khác trong đó chúng ta muốn tính toán lợi nhuận cho các danh mục sản phẩm của mình. Đây là một kịch bản phổ biến, ngay cả trong rất nhiều hướng dẫn. Chúng tôi có một bảng Doanh số trong mô hình dữ liệu của mình có dữ liệu giao dịch và có mối quan hệ giữa bảng Doanh số và bảng Danh mục Sản phẩm. Trong bảng Doanh số, chúng ta có một cột chứa doanh số và một cột khác có chi phí.

Chúng ta có thể tạo cột được tính toán để tính toán số tiền lợi nhuận cho mỗi hàng bằng cách trừ các giá trị trong cột COGS ra khỏi các giá trị trong cột SalesAmount, như thế này:

Bây giờ, chúng ta có thể tạo PivotTable và kéo trường Thể loại Sản phẩm vào CỘT và trường Lợi nhuận mới của chúng tôi vào vùng GIÁ TRỊ (một cột trong bảng trong PowerPivot là một Trường trong Danh sách Trường PivotTable). Kết quả là một số đo ngầm có tên là Tổng Lợi nhuận. Đó là số lượng giá trị tổng hợp từ cột lợi nhuận cho từng danh mục sản phẩm khác nhau. Kết quả của chúng tôi trông như thế này:

Trong trường hợp này, Lợi nhuận chỉ có ý nghĩa như một trường trong giá trị giá trị. Nếu chúng tôi đặt Lợi nhuận vào vùng CỘT, PivotTable của chúng tôi sẽ có dạng như sau:

Trường Lợi nhuận của chúng tôi không cung cấp bất kỳ thông tin hữu ích nào khi được đặt trong khu vực CỘT, HÀNG hoặc BỘ LỌC. Nó chỉ có ý nghĩa như là một giá trị tổng hợp trong vùng GIÁ TRỊ.

Những gì chúng tôi đã làm là tạo một cột có tên Là lợi nhuận để tính toán lợi nhuận cho mỗi hàng trong bảng Doanh số. Sau đó, chúng tôi đã thêm Lợi nhuận vào vùng GIÁ TRỊ của PivotTable, tự động tạo một số đo ẩn, trong đó kết quả được tính cho từng danh mục sản phẩm. Nếu bạn cho rằng chúng tôi thực sự tính lợi nhuận cho các danh mục sản phẩm của chúng tôi hai lần, bạn chính xác. Trước tiên, chúng tôi tính toán lợi nhuận cho mỗi hàng trong bảng Doanh số và sau đó chúng tôi đã thêm Lợi nhuận vào vùng GIÁ TRỊ, trong đó lợi nhuận được tổng hợp cho từng danh mục sản phẩm. Nếu bạn cũng đang nghĩ rằng chúng tôi không thực sự cần tạo cột Lợi nhuận được tính, bạn cũng chính xác. Nhưng làm thế nào để chúng ta tính lợi nhuận mà không tạo cột được tính Lợi nhuận?

Lợi nhuận, thực sự sẽ được tính toán tốt hơn như là một thước đo rõ ràng.

Hiện tại, chúng tôi sẽ để cột được tính Lợi nhuận trong bảng Doanh số và Danh mục Sản phẩm trong CỘT và Lợi nhuận trong GIÁ TRỊ của PivotTable để so sánh kết quả của chúng tôi.

Trong khu vực tính toán của bảng Doanh số, chúng tôi sẽ tạo một thước đo có tên là Tổng Lợi nhuận(để tránh đặt tên xung đột). Cuối cùng, nó sẽ cho kết quả giống như những gì chúng tôi đã làm trước đây, nhưng không có cột được tính Lợi nhuận.

Trước tiên, trong bảng Doanh số, chúng tôi chọn cột Số_doanh_số, rồi bấm vào Tự Tính tổng để tạo số đo Tổng Doanh_số_doanh_số rõràng. Hãy nhớ rằng, số đo rõ ràng là số đo mà chúng ta tạo trong khu vực tính toán của bảng trong Power Pivot. Chúng tôi làm tương tự cho cột COGS. Chúng tôi sẽ đổi tên Tổng Số_Doanh_số và Tổng COGS này để dễ xác định hơn.

Sau đó, chúng ta tạo một số đo khác với công thức này:

Tổng lợi nhuận:=[ Tổng Doanh_số_Doanh_số] - [Tổng COGS]

Sau khi thay đổi định dạng của số đo Tổng Lợi nhuận thành tiền tệ, chúng tôi có thể thêm nó vào PivotTable của mình.

Bạn có thể thấy số đo Tổng Lợi nhuận mới của chúng tôi trả về kết quả giống như tạo cột Lợi nhuận được tính rồi đặt nó trong GIÁ TRỊ. Sự khác biệt là thước đo Tổng Lợi nhuận của chúng tôi hiệu quả hơn rất nhiều và làm cho mô hình dữ liệu của chúng tôi gọn gàng và gọn gàng hơn vì chúng tôi đang tính toán tại thời điểm đó và chỉ cho các trường mà chúng tôi chọn cho PivotTable của mình. Chúng tôi thực sự không cần cột lợi nhuận được tính toán sau khi tất cả.

Tại sao phần cuối cùng này lại quan trọng? Cột được tính sẽ thêm dữ liệu vào mô hình dữ liệu và dữ liệu sẽ chiếm bộ nhớ. Nếu chúng ta làm mới mô hình dữ liệu, thì chúng tôi cũng cần có tài nguyên xử lý để tính toán lại tất cả các giá trị trong cột Lợi nhuận. Chúng tôi thực sự không cần phải tiếp nhận các tài nguyên như thế này vì chúng tôi thực sự muốn tính lợi nhuận của mình khi chọn các trường mà chúng tôi muốn Lợi nhuận cho trong PivotTable, chẳng hạn như các danh mục sản phẩm, khu vực hoặc theo ngày.

Chúng ta hãy xem xét một ví dụ khác. Trong đó cột được tính sẽ tạo ra các kết quả mà lúc đầu nhìn sơ qua trông chính xác, nhưng....

Trong ví dụ này, chúng tôi muốn tính toán doanh số dưới dạng tỷ lệ phần trăm của tổng doanh thu. Chúng tôi tạo cột được tính có tên % Doanh số trong bảng Doanh số của mình, như sau:

Các trạng thái công thức của chúng tôi: Đối với mỗi hàng trong bảng Doanh số, hãy chia số tiền trong cột SalesAmount cho tổng SUM của tất cả các số tiền trong cột SalesAmount.

Nếu chúng ta tạo PivotTable và thêm Danh mục Sản phẩm vào CỘT và chọn cột % Doanh số mới để đưa vào GIÁ TRỊ, chúng ta sẽ nhận được tổng % Doanh số cho từng danh mục sản phẩm của mình.

Ok. Điều này có vẻ tốt cho đến nay. Tuy nhiên, chúng ta hãy thêm slicer. Chúng tôi thêm Năm Lịch, rồi chọn một năm. Trong trường hợp này, chúng tôi chọn 2007. Đây là những gì chúng ta có được.

Nhìn thoáng qua, thông tin này có thể vẫn hiển thị chính xác. Nhưng, tỷ lệ phần trăm của chúng tôi thực sự nên tổng 100%, bởi vì chúng tôi muốn biết tỷ lệ phần trăm của tổng doanh thu cho mỗi danh mục sản phẩm của chúng tôi trong năm 2007. Vậy điều gì đã xảy ra?

Cột % Doanh số của chúng tôi tính toán tỷ lệ phần trăm cho mỗi hàng là giá trị trong cột Số_doanh_số được chia cho tổng cộng tất cả các giá trị trong cột SalesAmount. Các giá trị trong cột được tính toán được cố định. Chúng là kết quả không thay đổi cho mỗi hàng trong bảng. Khi chúng tôi thêm % Doanh số vào PivotTable, PivotTable đã được tổng hợp dưới dạng tổng tất cả các giá trị trong cột SalesAmount. Tổng của tất cả các giá trị trong cột % Doanh số sẽ luôn là 100%.

Chúng ta có thể xóa cột được tính % Doanh số vì nó sẽ không giúp chúng ta. Thay vào đó, Chúng tôi sẽ tạo một số đo để tính toán chính xác phần trăm tổng doanh thu của chúng tôi, bất kể bộ lọc hoặc slicer nào được áp dụng.

Bạn còn nhớ số đo TotalSalesAmount mà chúng ta đã tạo trước đó, số đo đơn giản là tính tổng cột SalesAmount? Chúng tôi đã sử dụng nó như một đối số trong thước đo Tổng Lợi nhuận của chúng tôi và chúng tôi sẽ sử dụng nó một lần nữa làm đối số trong trường được tính toán mới của chúng tôi.

Chúng tôi tạo một số đo mới với công thức sau đây:

% Tổng Doanh số:=([Tổng Số_Doanh_số]) / CALCULATE([Tổng Doanh_số], ALLSELECTED())

Công thức này cho biết: Chia kết quả từ Tổng Doanh_số_doanh_số cho tổng Doanh_số_doanh_số mà không có bất kỳ bộ lọc cột hoặc hàng nào khác với các bộ lọc đã xác định trong PivotTable.

Bây giờ, nếu chúng tôi thêm % mới của Tổng Doanh thu vào PivotTable, chúng tôi sẽ nhận được:

Trông khá hơn rồi. Bây giờ % Tổng Doanh thu của chúng tôi cho mỗi danh mục sản phẩm được tính toán dưới dạng tỷ lệ phần trăm của tổng doanh thu trong năm 2007. Nếu chúng tôi chọn một năm khác hoặc hơn một năm trong slicer Năm Dương lịch, chúng tôi sẽ nhận được tỷ lệ phần trăm mới cho các danh mục sản phẩm của mình, nhưng tổng cuối của chúng tôi vẫn là 100%. Chúng tôi cũng có thể thêm slicer và bộ lọc khác. Đơn vị đo % Tổng Doanh thu của chúng tôi sẽ luôn tạo ra tỷ lệ phần trăm của tổng doanh thu bất kể các slicer hoặc bộ lọc nào được áp dụng. Với các số đo, kết quả luôn được tính toán theo ngữ cảnh được xác định bởi các trường trong CỘT và HÀNG và theo bất kỳ bộ lọc hoặc slicer nào được áp dụng. Đây là sức mạnh của các biện pháp.

Dưới đây là một vài hướng dẫn để giúp bạn khi quyết định một cột được tính hay số đo phù hợp với một nhu cầu tính toán cụ thể:

Sử dụng cột được tính toán

• Nếu bạn muốn dữ liệu mới của mình xuất hiện trên HÀNG, CỘT hoặc trong BỘ LỌC trong PivotTable hoặc trên TRỤC, CHÚ GIẢI hoặc Ô XẾP THEO trong trực quan hóa Power View, bạn phải dùng cột được tính toán. Cũng giống như các cột dữ liệu thông thường, cột được tính toán có thể được dùng làm trường trong bất kỳ khu vực nào và nếu chúng là số, chúng cũng có thể được tổng hợp trong GIÁ TRỊ.

• Nếu bạn muốn dữ liệu mới của mình là giá trị cố định cho hàng. Ví dụ: bạn có một bảng ngày tháng với một cột ngày và bạn muốn một cột khác chỉ chứa số tháng. Bạn có thể tạo cột được tính toán chỉ tính số tháng từ các ngày trong cột Ngày. Ví dụ: =MONTH('Date'[Date]).

• Nếu bạn muốn thêm giá trị văn bản cho mỗi hàng vào bảng, hãy sử dụng cột được tính. Không bao giờ có thể tổng hợp các trường có giá trị văn bản trong GIÁ TRỊ. Ví dụ: =FORMAT('Date'[Date],"mmmm") cung cấp cho chúng tôi tên tháng cho mỗi ngày trong cột Ngày trong bảng Ngày.

Sử dụng số đo

• Nếu kết quả tính toán của bạn sẽ luôn phụ thuộc vào các trường khác mà bạn chọn trong PivotTable.

• Nếu bạn cần thực hiện các tính toán phức tạp hơn, chẳng hạn như tính toán số lượng dựa trên một bộ lọc gồm một số sắp xếp hoặc tính toán một năm trong năm hoặc phương sai, hãy sử dụng một trường được tính toán.

• Nếu bạn muốn duy trì kích cỡ sổ làm việc ở mức tối thiểu và tối đa hóa hiệu suất của sổ làm việc, hãy tạo càng nhiều phép tính càng đo càng tốt. Trong nhiều trường hợp, tất cả các phép tính của bạn có thể là phép đo, giảm đáng kể kích cỡ sổ làm việc và tăng tốc độ thời gian làm mới.

Hãy nhớ rằng không có gì sai khi tạo các cột được tính toán như chúng tôi đã làm với cột Lợi nhuận, rồi tổng hợp cột đó trong PivotTable hoặc báo cáo. Đây thực sự là một cách thực sự tốt và dễ dàng để tìm hiểu và tạo các phép tính của riêng bạn. Khi sự hiểu biết của bạn về hai tính năng cực kỳ mạnh mẽ này của Power Pivot tăng lên, bạn sẽ muốn tạo mô hình dữ liệu hiệu quả và chính xác nhất có thể. Hy vọng những gì bạn đã học được ở đây sẽ giúp ích cho bạn. Có một số tài nguyên thực sự tuyệt vời khác có thể giúp bạn. Đây chỉ là một số ít: Ngữ cảnh trong Công thức DAX, Tổng hợp trong Power Pivot và Trung tâm Tài nguyên DAX. Và mặc dù nâng cao hơn một chút và hướng tới các chuyên gia kế toán và tài chính, mô hình và phân tích Dữ liệu Lãi lỗ bằng Microsoft Power Pivot trong mẫu Excel được tải bằng các ví dụ công thức và lập mô hình dữ liệu tuyệt vời.