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ự nằm ở việc 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ó cột chứa các 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. Về bản chất, vì nó ở dạng số nên nó sẽ tự động được tính tổng, tính trung bình, đếm hoặc bất kỳ kiểu 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 số đo ngầm rất phù hợp để tổng hợp nhanh chóng và dễ dàng, nhưng chúng có giới hạn và hầu như luôn có thể khắc phục những giới hạn đó bằng các số đo rõ ràng và các cột được tính toán.

Trước tiên chúng ta hãy xem một ví dụ trong đó chúng ta sử dụng cột được tính để thêm một giá trị văn bản mới cho từng hàng trong một bảng có tên là 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 cho Tên sản phẩm, Màu sắc, Kích thước, Giá đại lý, v.v. Chúng ta có một bảng liên quan khác có tên là Thể loại Sản phẩm có chứa một cột Tên_sản_phẩm_Tên_thể_loại. Chúng tôi muốn 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, chúng ta có thể tạo một cột được tính toán 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 DAX RELATED để 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ể dùng một cột được tính để cộng 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.

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

Chúng ta có thể tạo cột được tính toán để tính 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 từ các giá trị trong cột SalesAmount, như thế này:

Bây giờ, chúng ta có thể tạo một 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. Đây là một lượng giá trị tổng hợp từ cột lợi nhuận cho mỗi 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Ị. Nếu chúng ta đặt Lợi nhuận vào vùng CỘT, PivotTable của chúng ta sẽ trông như thế này:

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 nó được đặt trong vùng CỘT, HÀNG HOẶC BỘ LỌC. Nó chỉ có nghĩa như một giá trị tổng hợp trong khu vực GIÁ TRỊ.

Những gì chúng tôi đã làm là tạo một cột tên là Lợi nhuận để tính toán biên lợi nhuận cho từng 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, tại đó kết quả được tính cho mỗi thể loại sản phẩm. Nếu bạn nghĩ rằng chúng tôi thực sự tính toán lợi nhuận cho các danh mục sản phẩm của mình hai lần, bạn đã đúng. 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ố, sau đó thêm Lợi nhuận vào vùng GIÁ TRỊ, nơi 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 nghĩ rằng chúng ta không thực sự cần tạo cột Tính Lợi nhuận, thì bạn cũng đúng. Nhưng, làm thế nào để chúng ta tính lợi nhuận của mình 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ư một thước đo rõ ràng.

Bây giờ, chúng ta sẽ để cột được tính Lợi nhuận trong bảng Doanh số và Thể loại Sản phẩm trong CỘT và Lợi nhuận trong GIÁ TRỊ của PivotTable để so sánh kết quả.

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

Trước tiên, trong bảng Sales, chúng ta chọn cột SalesAmount, rồi bấm AutoSum để tạo phép đo Tổng SalesAmount rõ ràng. Hãy nhớ rằng, một số đo rõ ràng là một 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 cũng làm tương tự với cột COGS. Chúng tôi sẽ đổi tên các Tổng Doanh số và Tổng Giá vốn bán hàng này để dễ xác định hơn.

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

Tổng Lợi nhuận:=[Tổng Doanh số] - [Tổng số tiền bán]

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

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

Tại sao phần cuối này lại quan trọng? Cột được tính 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, xử lý tài nguyên cũng cần thiết để tính toán lại tất cả các giá trị trong cột Lợi nhuận. Chúng ta thực sự không cần phải sử dụng những tài nguyên như thế này vì chúng ta thực sự muốn tính lợi nhuận của mình khi chọn các trường mong muốn Lợi nhuận trong PivotTable, chẳng hạn như danh mục sản phẩm, khu vực hoặc theo ngày.

Chúng ta hãy xem một ví dụ khác. Một nơi mà cột được tính toán tạo ra kết quả thoạt nhìn có vẻ đúng, nhưng....

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

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

Nếu chúng ta tạo một PivotTable, thêm Thể loại Sản phẩm vào CỘT và chọn cột % Doanh số mới để đặt vào GIÁ TRỊ, chúng ta sẽ có tổng cộng % Doanh số cho từng thể loại sản phẩm.

Được rồi. Điều này cho đến nay vẫn ổn. Nhưng hãy thêm một slicer. Chúng tôi thêm Calendar Year 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 tôi nhận được.

Thoạt nhìn, điều này vẫn có vẻ đúng. Tuy nhiên, tỷ lệ phần trăm của chúng tôi thực sự phải tổng cộng 100%, bởi vì chúng tôi muốn biết tỷ lệ phần trăm tổng doanh số 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 phần trăm cho mỗi hàng là giá trị trong cột SalesAmount chia cho tổng tất cả các giá trị trong cột SalesAmount. Các giá trị trong một cột được tính toán là cố định. Chúng là kết quả không thể thay đổi cho từng hàng trong bảng. Khi chúng tôi thêm % Doanh số vào PivotTable, nó được tổng hợp dưới dạng tổng của tất cả các giá trị trong cột SalesAmount. Tổng 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 cho 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 trên tổng doanh số, bất kể áp dụng bất kỳ bộ lọc hoặc slicer nào.

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

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

% trên Tổng Doanh số:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())

Công thức này xác định: Chia kết quả từ Total SalesAmount cho tổng cộng SalesAmount 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 ta thêm % Tổng Doanh thu mới vào PivotTable, chúng ta sẽ nhận được:

Có vẻ sẽ tốt hơn. Bây giờ, % trên Tổng Doanh thu của chúng tôi cho mỗi danh mục sản phẩm được tính dưới dạng phần trăm trên tổng doanh thu trong năm 2007. Nếu chúng ta chọn một năm khác hoặc nhiều hơn một năm trong slicer CalendarYear, chúng ta sẽ nhận được tỷ lệ phần trăm mới cho các thể loại sản phẩm của chúng ta, nhưng tổng cộng vẫn là 100%. Chúng ta cũng có thể thêm slicer và bộ lọc khác. Đơn vị đo % trên Tổng Doanh số của chúng tôi sẽ luôn tạo ra tỷ lệ phần trăm trên tổng doanh số, bất kể áp dụng slicer hay bộ lọc nào. Với giá trị đ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à bởi 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 quyết định xem cột được tính hay số đo có phù hợp với một nhu cầu tính toán cụ thể hay không:

Sử dụng các 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 CÁC 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 CẠNH NHAU THEO trong trực quan hóa Power View, bạn phải dùng cột được tính. Giống như các cột dữ liệu thông thường, các cột được tính toán có thể được sử dụng như một trường trong bất kỳ khu vực nào, và nếu chúng ở dạng số, chúng cũng có thể được tổng hợp thành 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ó bảng ngày chứa một cột ngày tháng và bạn muốn có một cột khác chỉ chứa số tháng. Bạn có thể tạo cột được tính chỉ tính số tháng từ 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 từng hàng vào bảng, hãy sử dụng cột được tính. Trường có giá trị văn bản không bao giờ được tổng hợp thành GIÁ TRỊ. Ví dụ, =FORMAT('Date'[Date],"mmmm") cung cấp cho chúng ta tên tháng cho từng ngày trong cột Ngày ở bảng Ngày tháng.

Sử dụng các thước đ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 bạn chọn trong PivotTable.
• Nếu bạn cần thực hiện các phép tính phức tạp hơn, chẳng hạn như tính toán số đếm dựa trên một bộ lọc thuộc một số loại hay tính toán theo năm hay phương sai, hãy dùng 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ác phép tính dưới dạng thước đo nhất có thể. Trong nhiều trường hợp, tất cả các tính toán của bạn có thể là số đo, giúp 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 ta đã làm với cột Lợi nhuận, rồi tổng hợp nó trong PivotTable hoặc báo cáo. Đó thực sự là một cách thực sự tốt và dễ dàng để tìm hiểu và tạo tính toán của riêng bạn. Khi hiểu rõ về hai tính năng cực kỳ mạnh mẽ này của Power Pivot ngày càng tăng, 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ẽ trợ giúp bạn. Có một số tài nguyên thực sự tuyệt vời khác cũng có thể giúp bạn. Đây chỉ là một vài tùy chọn: 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ù bản cập nhật nâng cao hơn một chút và hướng đến các chuyên gia kế toán và tài chính, mẫu Lập mô hình và Phân tích Dữ liệu Lợi nhuận và Lỗ với Microsoft Power Pivot trong Excel lại chứa các ví dụ về mô hình dữ liệu và công thức tuyệt vời.