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

Quan trọng:  Bài viết này là dịch máy, hãy xem tuyên bố miễn trừ trách nhiệm. Bạn hãy tìm phiên bản tiếng Anh của bài viết này tại đây để tham khảo.

Khi lần đầu tiên tìm hiểu cách dùng Power Pivot, hầu hết người dùng khám phá power thực nằm trong tổng hợp hoặc tính toán một kết quả trong một số cách. Nếu dữ liệu của bạn có một cột 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. Theo Thiên nhiên, vì nó là số, nó sẽ tự động được tóm tắt, Trung bình, đếm, hoặc bất kỳ loại phép tính gộp, bạn chọn. Điều này được gọi là một số đo ngầm. Ẩn thước đo được tuyệt vời cho phép tính gộp nhanh chóng và dễ, 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 với rõ ràng số đocột được tính toán.

Hãy cùng xem ví dụ mà chúng tôi sử dụng một cột được tính toán để thêm một giá trị văn bản mới cho mỗi hàng trong một 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 sắp xếp thông tin về mỗi sản phẩm chúng tôi bán hàng. Chúng tôi có cột cho tên sản phẩm, màu, kích cỡ, giá bán buôn, v.v... Chúng tôi có một bảng có liên quan khác có tên là thể loại sản phẩm có chứa một cột ProductCategoryName. Chúng tôi muốn dành cho mỗi sản phẩm trong bảng sản phẩm để bao gồm tên thể loại sản phẩm từ bảng thể loại sản phẩm. Trong bảng sản phẩm của chúng tôi, chúng tôi có thể tạo cột được tính toán có tên là thể loại sản phẩm như thế này:

Cột được Tính Thể loại Sản phẩm

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

Đây là một ví dụ về cách chúng tôi có thể sử dụng một cột được tính toán để thêm một giá trị cố định cho mỗi hàng mà chúng tôi có thể dùng phần sau trong các hàng, cột, hoặc bộ lọc khu vực của PivotTable hoặc báo cáo Power View.

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

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

Cột Lợi nhuận trong bảng Power Pivot

Bây giờ, chúng tôi 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 một 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. Nó là một số lượng tổng hợp của các giá trị từ cột lợi nhuận cho mỗi thể loại sản phẩm khác nhau. Kết quả của chúng tôi sẽ trông như thế này:

PivotTable đơn giản

Trong trường hợp này, lợi nhuận chỉ nghĩa là một trường trong giá trị. Nếu chúng tôi đã là đặt lợi nhuận trong vùng cột, PivotTable của chúng tôi sẽ trông như thế này:

PivotTable không chứa giá trị hữu ích

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 khi nó được đặt trong vùng bộ lọc, hàng hoặc cột. Chỉ nào thì nên là một giá trị tổng hợp trong khu vực giá trị.

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 một lợi nhuận cho mỗi hàng trong bảng Sales. Chúng tôi rồi thêm lợi nhuận vào vùng giá trị của chúng tôi PivotTable, tự động tạo một thước đo ngầm, nơi một kết quả được tính toán cho mỗi thể loại sản phẩm. Nếu bạn nghĩ đến chúng tôi thực sự tính toán lãi cho thể loại sản phẩm của chúng tôi hai lần, bạn là chính xác. Chúng tôi đầu tiên tính toán một lợi nhuận cho mỗi hàng trong bảng Sales, và chúng tôi rồi thêm lợi nhuận vào vùng giá trị mà nó đã được tổng hợp đối với mỗi thể loại sản phẩm. Nếu bạn cũng đang nghĩ chúng tôi không thực sự cần để tạo cột được tính toán lợi nhuận, bạn cũng là chính xác. Nhưng, làm thế nào rồi thực hiện chúng tôi tính lợi nhuận của chúng tôi mà không cần tạo một cột được tính toán lợi nhuận?

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

Bây giờ, chúng tôi sẽ rời khỏi lợi nhuận của chúng tôi cột được tính toá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 chúng tôi PivotTable, so sánh kết quả của chúng tôi.

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

Trước tiên, trong bảng Sales, chúng tôi chọn cột SalesAmount sau đó bấm tự động tính tổng để tạo một rõ ràng Tổng SalesAmountsố đo. Hãy nhớ, một thước đo rõ ràng là một chúng ta tạo trong vùng tính toán của một 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 các tổng SalesAmount tổng COGS làm chúng dễ dàng hơn để xác định.

Nút Tự động Tính tổng trong Power Pivot

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

Tổng lợi nhuận: = [ tổng SalesAmount]-[Total COGS]

Ghi chú: Chúng tôi cũng có thể viết công thức của chúng tôi như Tổng Profit:=SUM([SalesAmount]) - SUM([COGS]), nhưng bằng cách tạo thước đo SalesAmount tổng và tổng COGS riêng biệt, chúng tôi có thể dùng chúng trong PivotTable của chúng tôi quá, và chúng tôi có thể dùng chúng như đối số trong tất cả các loại công thức số đo khác.

Sau khi thay đổi định dạng của chúng tôi số đo mới 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 chúng tôi.

PivotTable

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

Tại sao là này phần cuối quan trọng? Cột được tính toán thêm dữ liệu vào mô hình dữ liệu, và dữ liệu cần lập bộ nhớ. Nếu chúng tôi làm mới mô hình dữ liệu, xử lý tài nguyên cũng cần có để 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 mất tài nguyên như thế này vì chúng tôi thực sự muốn tính toán lợi nhuận của chúng tôi khi chúng tôi chọn trường mà chúng tôi muốn lợi nhuận cho trong PivotTable, như thể loại sản phẩm, khu vực, hoặc theo ngày tháng.

Hãy xem ví dụ khác. Một nơi một cột được tính toán sẽ tạo ra kết quả thoạt trông đúng, nhưng...

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

Cột được Tính % Doanh số

Trạng thái thức của chúng tôi: đối với mỗi hàng trong bảng Sales, chia giá trị trong cột SalesAmount tổng trong tất cả các số trong cột SalesAmount.

Nếu chúng ta tạo PivotTable và thêm thể loại sản phẩm vào cột và chọn cột % doanh số mới để đặt nó vào giá trị, chúng tôi nhận được con số tổng cộng tổng % doanh số cho mỗi thể loại sản phẩm của chúng tôi.

PivotTable hiển thị Tổng % Doanh số cho các Thể loại Sản phẩm

OK. Điều này có vẻ phù hợp cho đến nay. Nhưng, hãy thêm slicer. Chúng tôi thêm lịch năm và sau đó 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.

Kết quả Tổng % Doanh số không chính xác trong PivotTable

Nháy mắt đầu tiên, điều 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 nên thực sự tổng 100%, vì chúng tôi muốn tìm hiểu tỷ lệ phần trăm của tổng doanh thu đối với mỗi thể loại sản phẩm của chúng tôi để 2007. Vì vậy điều gì không ổn?

Chúng tôi % doanh số cột tính toán một phần trăm cho mỗi hàng là giá trị trong cột SalesAmount chia cho tổng của tất cả giá trị trong cột SalesAmount. Giá trị trong một cột được tính toán được cố định. Họ đủ ra kết quả không thay đổi đối với mỗi hàng trong bảng. Khi chúng tôi đã thêm % doanh số của chúng tôi pivottable Quantities được tổng hợp như tổng của tất cả giá trị trong cột SalesAmount. Đó tổng của tất cả giá trị trong cột doanh số % sẽ luôn là 100%.

Mẹo: Đảm bảo rằng bạn đọc ngữ cảnh trong công thức DAX. Bạn sẽ tìm hiểu về tốt cấp ngữ cảnh hàng và bộ lọc ngữ cảnh, đây là những gì chúng tôi mô tả ở đây.

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

Hãy nhớ rằng số đo TotalSalesAmount chúng tôi đã tạo trước đó, mà chỉ cần tính tổng cột SalesAmount? Chúng ta đã dùng nó làm đối số trong các số đo tổng lợi nhuận của chúng tôi, và chúng tôi sẽ dùng nó lại làm đối số trong trường được tính toán mới của chúng tôi.

Mẹo: Tạo số đo rõ ràng như SalesAmount tổng và tổng COGS không chỉ hữu ích bản thân họ trong PivotTable hoặc báo cáo, nhưng chúng sẽ cũng hữu ích làm đối số trong các số đo khác khi bạn cần kết quả là một đối số. Điều này làm cho công thức của bạn hiệu quả hơn và dễ đọc hơn. Đây là dữ liệu tốt lập mô hình thực hành.

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

% của tổng doanh thu: =([Total SalesAmount]) / CALCULATE ([tổng số SalesAmount], ALLSELECTED())

Chỉ rõ công thức này: chia kết quả từ tổng SalesAmount tổng cộng tổng SalesAmount mà không cần mọi bộ lọc cột hoặc hàng khác với những người được xác định trong PivotTable.

Mẹo: Đảm bảo rằng bạn đọc về hàm CALCULATEALLSELECTED trong tham chiếu DAX.

Bây giờ, nếu chúng tôi thêm mới của chúng tôi % của tổng doanh thu trong pivottable, chúng tôi nhận được:

Kết quả Tổng % Doanh số chính xác trong PivotTable

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

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

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

  • Nếu bạn muốn dữ liệu mới của bạn 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 cạnh nhau theo trong trực quan hóa Power View, bạn phải sử dụng một cột được tính toán. Giống như các dữ liệu thường xuyên cột, cột được tính toán có thể được dùng làm trường trong khu vực bất kỳ, và nếu chúng được số họ có thể được tổng hợp trong các giá trị quá.

  • Nếu bạn muốn dữ liệu mới của bạn là một 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 tháng, và bạn muốn một cột chứa số tháng. Bạn có thể tạo cột được tính toán chỉ số tháng từ ngày tháng trong cột ngày tháng. 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 dùng một cột được tính toán. Các trường có giá trị văn bản có thể không bao giờ được tổng hợp trong các giá trị. Ví dụ, =FORMAT('Date'[Date],"mmmm") cho chúng tôi tên tháng cho mỗi ngày trong cột ngày tháng trong bảng ngày tháng.

Sử dụng các số đo

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

  • Nếu bạn cần phải thực hiện 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 của một số sắp xếp, hoặc tính toán một năm qua, năm, hoặc phương sai, dùng trường được tính.

  • Nếu bạn muốn giữ lại kích cỡ sổ làm việc của bạn để tối thiểu và tối đa hóa hiệu năng của nó, tạo làm nhiều phép tính của bạn dưới dạng các số đ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à số đo, đáng kể việc giảm kích cỡ sổ làm việc và đẩy khoảng thời gian làm mới.

Giữ lại trong ý, không ổn với việc tạo cột được tính toán như chúng ta làm với cột lợi nhuận của chúng tôi, và sau đó tổng hợp trong PivotTable hoặc báo cáo. Thật sự một thực sự tốt và dễ dàng để tìm hiểu về và tạo phép tính riêng của bạn. Khi bạn tìm hiểu về các tính năng hai lớn quá mạnh mẽ của Power Pivot phát triển, bạn sẽ muốn tạo mô hình dữ liệu hiệu quả nhất và chính xác bạn có thể. Hopefully những gì bạn đã học ở đây sẽ giúp. Không có một số thực sự tuyệt vời nguồn tài nguyên khác ra có mà cũng có thể giúp bạn. Dưới đây là chỉ với một vài: ngữ cảnh trong công thức DAX, hàm tính gộp trong Power PivotDAX Trung tâm tài nguyên. Và trong khi nó có hơn một chút nâng cao, hướng kế toán và chuyên gia tài chính, phân tích với Microsoft PowerPivot trong Excel và lập mô hình dữ liệu lợi nhuận và tổn thất mẫu được tải với lập mô hình dữ liệu tuyệt vời và ví dụ công thức.

Ghi chú: Tuyên bố miễn trừ trách nhiệm Dịch Máy: Bài viết này do một hệ thống máy tính dịch mà không có sự can thiệp của con người. Microsoft cung cấp những bản dịch máy này để giúp người dùng không nói tiếng Anh hiểu nội dung về các sản phẩm, dịch vụ và công nghệ của Microsoft. Do bài viết này được dịch máy nên có thể có các lỗi về từ vựng, cú pháp hoặc ngữ pháp.

Phát triển các kỹ năng của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×