Các hàm tính gộp trong Power Pivot

Tổng hợp là một cách thu nhỏ, tóm tắt hoặc nhóm dữ liệu. Khi bạn bắt đầu với dữ liệu thô từ bảng hoặc các nguồn dữ liệu khác, dữ liệu thường phẳng, có nghĩa là có nhiều chi tiết, nhưng chưa được sắp xếp hoặc nhóm theo bất kỳ cách nào. Việc thiếu tóm tắt hoặc cấu trúc này có thể làm cho việc khám phá các mẫu hình trong dữ liệu khó khăn. Một phần quan trọng của lập mô hình dữ liệu là xác định các kết tập giúp đơn giản hóa, trừu tượng hoặc tóm tắt các mẫu hình để trả lời cho một câu hỏi kinh doanh cụ thể.

Hầu hết các phép tổng hợp phổ biến nhất, chẳng hạn như các phép tổng hợp sử dụng AVERAGE, COUNT, DISTINCTCOUNT, MAX, MIN hoặc SUM có thể được tạo tự động trong một số đo bằng cách sử dụng tính năng Tự Tính tổng. Các kiểu tổng hợp khác, chẳng hạn như AVERAGEX, COUNTX,COUNTROWS hoặc SUMXtrả về bảng và yêu cầu công thức được tạo bằng Biểu thức Phân tích Dữ liệu (DAX).

Tìm hiểu về Tổng hợp trong Power Pivot

Chọn Nhóm cho Tính tổng hợp

Khi tổng hợp dữ liệu, bạn nhóm dữ liệu theo các thuộc tính như sản phẩm, giá, khu vực hoặc ngày, rồi xác định công thức hoạt động trên tất cả dữ liệu trong nhóm. Ví dụ: khi bạn tạo tổng trong một năm, bạn đang tạo phép tổng hợp. Nếu sau đó bạn tạo tỷ lệ năm nay so với năm trước và trình bày chúng dưới dạng tỷ lệ phần trăm, thì đó là một kiểu tổng hợp khác.

Quyết định về cách nhóm dữ liệu do câu hỏi kinh doanh đưa ra. Ví dụ, phép tổng hợp có thể trả lời các câu hỏi sau đây:

Đếm Có bao nhiêu giao dịch đã có trong một tháng?

Trung bình Doanh số trung bình của nhân viên bán hàng trong tháng này là bao nhiều?

Giá trị tối thiểu và tối đa Những khu bán hàng nào là năm địa điểm bán hàng đầu về số lượng đơn vị được bán?

Để tạo một phép tính trả lời những câu hỏi này, bạn phải có dữ liệu chi tiết có chứa các số cần đếm hoặc tính tổng, và dữ liệu số phải có liên quan theo một cách nào đó với các nhóm mà bạn sẽ dùng để sắp xếp kết quả.

Nếu dữ liệu chưa chứa các giá trị mà bạn có thể sử dụng để nhóm, chẳng hạn như danh mục sản phẩm hoặc tên của khu vực địa lý có chứa cửa hàng, bạn có thể muốn đưa nhóm vào dữ liệu của mình bằng cách thêm thể loại. Khi tạo nhóm trong Excel, bạn phải nhập hoặc chọn thủ công các nhóm mà bạn muốn sử dụng giữa các cột trong trang tính của mình. Tuy nhiên, trong một hệ thống quan hệ, các cấu trúc phân cấp như thể loại cho sản phẩm thường được lưu trữ trong một bảng khác với bảng dữ kiện hoặc bảng giá trị. Thông thường, bảng thể loại được liên kết với dữ liệu dữ kiện bằng một loại khóa nào đó. Ví dụ: giả sử bạn thấy dữ liệu của mình chứa ID sản phẩm chứ không phải tên sản phẩm hoặc danh mục sản phẩm. Để thêm thể loại vào một trang tính Excel phẳng, bạn sẽ phải sao chép trong cột có chứa tên thể loại. Với Power Pivot, bạn có thể nhập bảng thể loại sản phẩm vào mô hình dữ liệu của mình, tạo mối quan hệ giữa bảng có dữ liệu số và danh sách thể loại sản phẩm, rồi sử dụng các danh mục để nhóm dữ liệu. Để biết thêm thông tin, hãy Create mối quan hệ giữa các bảng.

Chọn một Hàm để Tổng hợp

Sau khi bạn đã xác định và thêm các nhóm để sử dụng, bạn phải quyết định dùng hàm toán học nào để tổng hợp. Thông thường, tổng hợp từ được sử dụng làm từ đồng nghĩa cho các phép toán học hoặc thống kê được sử dụng trong tổng hợp, chẳng hạn như tổng, trung bình, tối thiểu hoặc số đếm. Tuy nhiên, Power Pivot cho phép bạn tạo công thức tùy chỉnh cho phép tổng hợp, ngoài các kết tập tiêu chuẩn được tìm thấy trong cả Power Pivot và Excel.

Ví dụ: với cùng một tập hợp giá trị và nhóm được sử dụng trong các ví dụ trước, bạn có thể tạo các tổng hợp tùy chỉnh trả lời các câu hỏi sau đây:

Số lượng đã lọc Có bao nhiêu giao dịch trong một tháng, ngoại trừ thời hạn bảo trì cuối tháng?

Tỷ lệ sử dụng trung bình theo thời gian Mức tăng trưởng hoặc giảm doanh số so với cùng kỳ năm ngoái là bao nhiêu?

Các giá trị tối thiểu và tối đa được nhóm Các khu bán hàng nào được xếp hạng hàng đầu cho từng danh mục sản phẩm hoặc cho mỗi khuyến mãi bán hàng?

Thêm Tổng hợp vào Công thức và PivotTable

Khi bạn có ý tưởng chung về cách nhóm dữ liệu của mình thành có ý nghĩa và các giá trị mà bạn muốn làm việc cùng, bạn có thể quyết định nên xây dựng PivotTable hay tạo phép tính trong bảng. Power Pivot mở rộng và cải thiện khả năng tổng hợp riêng của Excel, chẳng hạn như tổng, số đếm hoặc giá trị trung bình. Bạn có thể tạo các phép tổng hợp tùy Power Pivot trong cửa sổ Power Pivot hoặc trong vùng PivotTable của Excel.

Trong cột được tính, bạn có thể tạo các phép tổng hợp có tính đến ngữ cảnh hàng hiện tại để truy xuất các hàng liên quan từ một bảng khác, rồi tính tổng, đếm hoặc tính trung bình các giá trị đó trong các hàng liên quan.
Trong một số đo, bạn có thể tạo các kết tập động sử dụng cả hai bộ lọc được xác định trong công thức và các bộ lọc được áp dụng theo thiết kế của PivotTable cũng như lựa chọn Slicer, đầu đề cột và đầu đề hàng. Các số đo sử dụng phép tổng hợp chuẩn có thể được tạo Power Pivot bằng cách dùng Tự Tính tổng hoặc bằng cách tạo công thức. Bạn cũng có thể tạo các số đo ẩn bằng cách dùng tổng hợp chuẩn trong PivotTable trong Excel.

Thêm Nhóm vào PivotTable

Khi bạn thiết kế Một PivotTable, bạn kéo các trường đại diện cho các nhóm, thể loại hoặc cấu trúc phân cấp, vào phần cột và hàng của PivotTable để nhóm dữ liệu. Sau đó, bạn kéo các trường có chứa giá trị số vào vùng giá trị để chúng có thể được đếm, tính trung bình hoặc tính tổng.

Nếu bạn thêm thể loại vào PivotTable nhưng dữ liệu thể loại không liên quan đến dữ liệu dữ liệu thực tế, bạn có thể gặp lỗi hoặc kết quả đặc biệt. Thông thường Power Pivot sẽ cố gắng khắc phục sự cố, bằng cách tự động phát hiện và đề xuất mối quan hệ. Để biết thêm thông tin, hãy xem Làm việc với Mối quan hệ trong PivotTable.

Bạn cũng có thể kéo các trường vào Slicer để chọn một số nhóm dữ liệu nhất định để xem. Slicer cho phép bạn nhóm, sắp xếp và lọc kết quả một cách tương tác trong PivotTable.

Làm việc với Nhóm trong công thức

Bạn cũng có thể dùng các nhóm và thể loại để tổng hợp dữ liệu được lưu trữ trong bảng bằng cách tạo mối quan hệ giữa các bảng, sau đó tạo công thức tận dụng các mối quan hệ đó để tra cứu các giá trị liên quan.

Nói cách khác, nếu bạn muốn tạo công thức nhóm các giá trị theo thể loại, trước tiên bạn cần sử dụng mối quan hệ để kết nối bảng chứa dữ liệu chi tiết và bảng có chứa thể loại, rồi xây dựng công thức.

Để biết thêm thông tin về cách tạo công thức sử dụng tra cứu, hãy xem Tra cứu trong Công thức Power Pivot.

Sử dụng Bộ lọc trong Phép tổng hợp

Một tính năng mới trong Power Pivot là khả năng áp dụng bộ lọc cho các cột và bảng dữ liệu, không chỉ trong giao diện người dùng và trong PivotTable hoặc biểu đồ, mà còn trong chính các công thức mà bạn sử dụng để tính toán tổng hợp. Bộ lọc có thể được dùng trong công thức cả trong cột được tính toán và theo dạng s.

Ví dụ, trong các hàm tổng hợp DAX mới, thay vì chỉ định các giá trị để tính tổng hoặc đếm, bạn có thể chỉ định toàn bộ bảng làm đối số. Nếu bạn không áp dụng bất kỳ bộ lọc nào cho bảng đó, hàm tổng hợp sẽ hoạt động với tất cả các giá trị trong cột được chỉ định của bảng. Tuy nhiên, trong DAX, bạn có thể tạo bộ lọc động hoặc tĩnh trên bảng để phép tổng hợp hoạt động dựa trên một tập hợp con dữ liệu khác tùy thuộc vào điều kiện lọc và ngữ cảnh hiện tại.

Bằng cách kết hợp các điều kiện và bộ lọc trong công thức, bạn có thể tạo phép tổng hợp thay đổi tùy theo các giá trị được cung cấp trong công thức hoặc thay đổi tùy thuộc vào việc lựa chọn đầu đề hàng và đầu đề cột trong PivotTable.

Để biết thêm thông tin, hãy xem mục Lọc Dữ liệu trong Công thức.

So sánh các hàm Tổng hợp Excel và Hàm Tổng hợp DAX

Bảng sau đây liệt kê một số hàm tổng hợp tiêu chuẩn do Excel cung cấp và cung cấp liên kết đến việc thực hiện các hàm này trong Power Pivot. Phiên bản DAX của các hàm này hoạt động giống như phiên bản Excel, với một số khác biệt nhỏ về cú pháp và cách xử lý các kiểu dữ liệu nhất định.

Hàm Tổng hợp Tiêu chuẩn

Hàm	Sử dụng
TRUNG BÌNH	Trả về trung bình (trung bình số học) của tất cả các số trong một cột.
HÀM AVERAGEA	Trả về trung bình (trung bình cộng) của tất cả các giá trị trong một cột. Xử lý các giá trị văn bản và không phải số.
ĐẾM	Đếm số lượng giá trị số trong một cột.
HÀM COUNTA	Đếm số lượng giá trị trong một cột không trống.
MAX	Trả về giá trị số lớn nhất trong một cột.
MAXX	Trả về giá trị lớn nhất từ một tập hợp các biểu thức được định trị trên một bảng.
PHÚT	Trả về giá trị số nhỏ nhất trong một cột.
HÀM MINX	Trả về giá trị nhỏ nhất từ một tập hợp các biểu thức được định trị trên một bảng.
TỔNG	Cộng tất cả các số trong một cột.

Hàm Tổng hợp DAX

DAX bao gồm các hàm tổng hợp cho phép bạn xác định bảng để thực hiện phép tổng hợp. Do đó, thay vì chỉ thêm hoặc trung bình các giá trị trong một cột, những hàm này cho phép bạn tạo một biểu thức tự động xác định dữ liệu để tổng hợp.

Bảng sau đây liệt kê các hàm tổng hợp sẵn dùng trong DAX.

Hàm	Sử dụng
AVERAGEX	Tính trung bình một tập hợp các biểu thức được định trị trên một bảng.
HÀM COUNTAX	Đếm một tập hợp các biểu thức được định trị trên một bảng.
COUNTBLANK	Đếm số giá trị trống trong một cột.
COUNTX	Đếm tổng số hàng trong một bảng.
SỐ ĐẾM	Đếm số hàng được trả về từ hàm bảng lồng nhau, chẳng hạn như hàm lọc.
Hàm SUMX	Trả về tổng của một tập hợp biểu thức được định trị trên một bảng.

Sự khác nhau giữa hàm Tổng hợp DAX và Excel

Mặc dù các hàm này có cùng tên với phiên bản Excel, chúng sử dụng công cụ phân tích trong bộ nhớ của Power Pivot và đã được viết lại để làm việc với bảng và cột. Bạn không thể dùng công thức DAX trong sổ làm việc Excel và ngược lại. Chúng chỉ có thể được dùng trong cửa sổ Power Pivot và trong PivotTable dựa trên dữ Power Pivot của bạn. Ngoài ra, mặc dù các hàm có tên giống hệt nhau nhưng hành vi có thể hơi khác nhau. Để biết thêm thông tin, hãy xem các chủ đề tham khảo về hàm riêng lẻ.

Cách các cột được đánh giá trong phép tổng hợp cũng khác với cách Excel xử lý phép tổng hợp. Ví dụ có thể giúp minh họa.

Giả sử bạn muốn có được tổng các giá trị trong cột Số lượng trong bảng Doanh số, vì vậy bạn tạo công thức sau đây:

=SUM('Sales'[Amount])

Trong trường hợp đơn giản nhất, hàm lấy giá trị từ một cột chưa được lọc duy nhất và kết quả giống như trong Excel, hàm này luôn luôn cộng các giá trị trong cột, Amount. Tuy nhiên, trong Power Pivot, công thức được hiểu là "Lấy giá trị bằng Số lượng cho mỗi hàng của bảng Doanh số, rồi cộng các giá trị riêng lẻ đó. Power Pivot định trị mỗi hàng mà phép tổng hợp được thực hiện và tính toán giá trị vô hướng đơn cho mỗi hàng, rồi thực hiện tổng hợp trên các giá trị đó. Do đó, kết quả của công thức có thể khác nhau nếu bộ lọc đã được áp dụng cho bảng hoặc nếu các giá trị được tính toán dựa trên các phép tổng hợp khác có thể được lọc. Để biết thêm thông tin, hãy xem Ngữ cảnh trong Công thức DAX.

Hàm Thông minh Thời gian DAX

Ngoài các hàm tổng hợp bảng được mô tả trong mục trước, DAX có các hàm tổng hợp hoạt động với ngày và giờ bạn chỉ định, để cung cấp hiển thị thời gian thông minh dựng sẵn. Những hàm này sử dụng phạm vi ngày để lấy các giá trị liên quan và tổng hợp các giá trị. Bạn cũng có thể so sánh các giá trị qua các phạm vi ngày.

Bảng sau đây liệt kê các hàm hiển thị thời gian thông minh có thể được sử dụng để tổng hợp.

Hàm	Sử dụng
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR	Tính toán một giá trị ở cuối lịch của kỳ đã cho.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR	Tính toán một giá trị ở cuối lịch của khoảng thời gian trước kỳ đã cho.
TOTALMTD HÀM TOTALYTD TOTALQTD	Tính toán một giá trị trong khoảng thời gian bắt đầu vào ngày đầu tiên của khoảng thời gian và kết thúc vào ngày gần nhất trong cột ngày được chỉ định.

Các hàm khác trong mục Hàm Hiển thị Thời gian Thông minh (Hàm Hiển thị Thời gian Thông minh) là các hàm có thể được dùng để truy xuất ngày tháng hoặc phạm vi ngày tùy chỉnh để sử dụng trong kết tập. Ví dụ, bạn có thể dùng hàm DATESINPERIOD để trả về phạm vi ngày tháng và dùng tập hợp ngày đó làm đối số cho một hàm khác để tính toán phép tổng hợp tùy chỉnh chỉ cho những ngày đó.