Các tình huống DAX trong Power Pivot

Phần này cung cấp các nối kết đến các ví dụ thể hiện việc sử dụng các công thức DAX trong các kịch bản sau đây.

  • Thực hiện các phép tính phức tạp

  • Làm việc với văn bản và ngày tháng

  • Các giá trị có điều kiện và kiểm tra lỗi

  • Sử dụng thời gian thông minh

  • Xếp hạng và so sánh các giá trị

Trong bài viết này

Bắt đầu

Tình huống: thực hiện phép tính phức tạp

Tạo các phép tính tùy chỉnh cho PivotTable

Áp dụng bộ lọc cho một công thức

Loại bỏ các bộ lọc có chọn lọc để tạo tỷ lệ động

Sử dụng một giá trị từ vòng lặp bên ngoài

Tình huống: làm việc với văn bản và ngày tháng

Tạo cột khóa bằng cách ghép nối

Soạn ngày tháng dựa trên các phần ngày được trích xuất từ ngày văn bản

Xác định một ngày tùy chỉnh hoặc định dạng số

Thay đổi kiểu dữ liệu bằng cách dùng công thức

Kịch bản: giá trị có điều kiện và kiểm tra lỗi

Tạo một giá trị dựa trên một điều kiện

Kiểm tra lỗi trong một công thức

Tình huống: sử dụng thời gian thông minh

Tính toán bán hàng tích lũy

So sánh các giá trị theo thời gian

Tính giá trị trong phạm vi ngày tùy chỉnh

Tình huống: xếp hạng và so sánh các giá trị

Chỉ hiển thị mười mục hàng đầu trong PivotTable

Các mục đơn hàng động bằng cách sử dụng công thức

Bắt đầu

Truy nhập Trung tâm tài nguyên Dax , nơi bạn có thể tìm thấy tất cả các loại thông tin về Dax bao gồm blog, mẫu, Whitepapers và video được cung cấp bởi các chuyên gia hàng đầu trong ngành và Microsoft.

Tình huống: thực hiện phép tính phức tạp

Công thức DAX có thể thực hiện các phép tính phức tạp liên quan đến tính năng chỉnh hình tùy chỉnh, lọc và sử dụng các giá trị có điều kiện. Phần này cung cấp ví dụ về cách bắt đầu với các phép tính tùy chỉnh.

Tạo các phép tính tùy chỉnh cho PivotTable

TÍNH toán và CALCULATETABLE là các hàm mạnh mẽ, linh hoạt, hữu ích cho việc xác định trường được tính toán. Những hàm này cho phép bạn thay đổi ngữ cảnh trong đó các phép tính sẽ được thực hiện. Bạn cũng có thể tùy chỉnh kiểu tập hợp hoặc thao tác toán học để thực hiện. Xem các chủ đề sau đây để biết ví dụ.

Áp dụng bộ lọc cho một công thức

Ở hầu hết các vị trí mà hàm DAX có một bảng làm đối số, bạn thường có thể vượt qua trong một bảng đã lọc thay vào đó, bằng cách sử dụng hàm FILTER thay vì tên bảng, hoặc bằng cách xác định một biểu thức bộ lọc là một trong các đối số của hàm. Các chủ đề sau đây cung cấp ví dụ về cách tạo bộ lọc và cách bộ lọc ảnh hưởng đến kết quả của công thức. Để biết thêm thông tin, hãy xem lọc dữ liệu trong công thức Dax.

Hàm FILTER cho phép bạn xác định tiêu chí bộ lọc bằng cách sử dụng một biểu thức, trong khi các hàm khác được thiết kế đặc biệt để lọc các giá trị trống.

Loại bỏ các bộ lọc có chọn lọc để tạo tỷ lệ động

Bằng cách tạo các bộ lọc động trong công thức, bạn có thể dễ dàng trả lời các câu hỏi như sau:

  • Sự đóng góp của doanh số bán hàng của sản phẩm hiện tại cho tổng doanh thu cho năm là gì?

  • Bộ phận này có bao nhiêu đóng góp vào tổng lợi nhuận cho tất cả các năm hoạt động, so với các đơn vị khác?

Các công thức mà bạn sử dụng trong PivotTable có thể bị ảnh hưởng bởi ngữ cảnh PivotTable, nhưng bạn có thể thay đổi chọn lọc ngữ cảnh bằng cách thêm hoặc loại bỏ bộ lọc. Ví dụ trong chủ đề ALL sẽ hướng dẫn bạn cách thực hiện việc này. Để tìm tỷ lệ bán hàng cho một người bán lại cụ thể trong doanh số bán hàng cho tất cả các người bán lại, bạn sẽ tạo một thước đo tính toán giá trị cho ngữ cảnh hiện tại được chia theo giá trị cho ngữ cảnh tất cả.

Chủ đề ALLEXCEPT cung cấp một ví dụ về cách có chọn lọc các bộ lọc trong công thức. Cả hai ví dụ đều hướng dẫn bạn qua cách thức thay đổi kết quả tùy thuộc vào thiết kế của PivotTable.

Để biết các ví dụ khác về cách tính toán tỷ lệ và tỷ lệ phần trăm, hãy xem các chủ đề sau đây:

Sử dụng một giá trị từ vòng lặp bên ngoài

Ngoài việc sử dụng các giá trị từ ngữ cảnh hiện tại trong tính toán, DAX có thể sử dụng một giá trị từ một vòng lặp trước đó trong việc tạo một tập hợp các phép tính liên quan. Chủ đề sau đây cung cấp một hướng về cách xây dựng công thức tham chiếu một giá trị từ một vòng lặp bên ngoài. Hàm trước đó hỗ trợ tối đa hai cấp độ của các vòng được lồng nhau.

Để tìm hiểu thêm về ngữ cảnh hàng và bảng liên quan, cũng như cách sử dụng khái niệm này trong công thức, hãy xem ngữ cảnh trong công thức Dax.

Tình huống: làm việc với văn bản và ngày tháng

Phần này cung cấp các nối kết đến các chủ đề tham chiếu DAX có chứa ví dụ về các kịch bản phổ biến liên quan đến làm việc với văn bản, trích xuất và soạn các giá trị ngày và thời gian, hoặc tạo giá trị dựa trên một điều kiện.

Tạo cột khóa bằng cách ghép nối

Power Pivot không cho phép các phím composite; do đó, nếu bạn có các phím tổng hợp trong nguồn dữ liệu của mình, bạn có thể cần kết hợp chúng thành một cột khóa đơn. Chủ đề sau đây cung cấp một ví dụ về cách tạo cột được tính toán dựa trên một khóa tổng hợp.

Soạn ngày tháng dựa trên các phần ngày được trích xuất từ ngày văn bản

Power Pivot sử dụng kiểu dữ liệu ngày/giờ SQL Server để làm việc với ngày; do đó, nếu dữ liệu ngoài của bạn chứa các ngày được định dạng khác-ví dụ: nếu ngày của bạn được viết ở định dạng ngày trong khu vực không được nhận diện bởi công cụ dữ liệu Power Pivot, hoặc nếu dữ liệu của bạn sử dụng các phím thay thế số nguyên--bạn có

Ví dụ, nếu bạn có một cột ngày đã được biểu thị dưới dạng số nguyên và sau đó được nhập dưới dạng chuỗi văn bản, bạn có thể chuyển đổi chuỗi thành giá trị ngày/thời gian bằng cách dùng công thức sau đây:

= DATE (RIGHT ([Value1], 4), LEFT ([Value1], 2), MID ([Value1], 2))

Value1

Kết quả

01032009

1/3/2009

12132008

12/13/2008

06252007

6/25/2007

Các chủ đề sau đây cung cấp thêm thông tin về các hàm được dùng để trích xuất và soạn ngày.

Xác định một ngày tùy chỉnh hoặc định dạng số

Nếu dữ liệu của bạn chứa ngày hoặc số điện thoại không được thể hiện ở một trong các định dạng văn bản Windows chuẩn, bạn có thể xác định một định dạng tùy chỉnh để đảm bảo rằng các giá trị được xử lý đúng cách. Những định dạng này được sử dụng khi chuyển đổi các giá trị thành chuỗi hoặc từ chuỗi. Các chủ đề sau đây cũng cung cấp một danh sách chi tiết về các định dạng được xác định trước sẵn dùng để làm việc với ngày và số điện thoại.

Thay đổi kiểu dữ liệu bằng cách dùng công thức

Trong Power Pivot, kiểu dữ liệu của đầu ra được xác định bởi các cột nguồn và bạn không thể xác định rõ kiểu dữ liệu của kết quả, vì kiểu dữ liệu tối ưu được xác định bởi Power Pivot. Tuy nhiên, bạn có thể sử dụng các chuyển đổi kiểu dữ liệu ẩn được thực hiện bằng Power Pivot để thao tác với kiểu dữ liệu đầu ra. Để biết thêm thông tin về loại chuyển đổi, hãy xem lấy dữ liệu mẫu cho hướng dẫn mô hình dữ liệu và Dax.

  • Để chuyển đổi một ngày hoặc chuỗi số vào một số, nhân với 1,0. Ví dụ, công thức sau tính toán ngày hiện tại trừ 3 ngày và sau đó kết quả giá trị số nguyên tương ứng.

    = (HÔM NAY ()-3) * 1.0

  • Để chuyển đổi giá trị ngày, số hoặc tiền tệ vào một chuỗi, hãy ghép giá trị với một chuỗi trống. Ví dụ, công thức sau đây trả về ngày hôm nay thành chuỗi.

    = "" & TODAY ()

Các hàm sau đây cũng có thể được sử dụng để đảm bảo rằng một kiểu dữ liệu cụ thể được trả về:

Chuyển đổi số thực thành số nguyên

Kịch bản: giá trị có điều kiện và kiểm tra lỗi

Chẳng hạn như Excel, DAX có các hàm cho phép bạn kiểm tra các giá trị trong dữ liệu và trả về một giá trị khác dựa trên một điều kiện. Ví dụ, bạn có thể tạo một cột được tính toán có nhãn đại lý hoặc là giá trịưu tiên hoặc tùy thuộc vào số tiền doanh số hàng năm. Các hàm có các giá trị kiểm tra cũng hữu ích khi kiểm tra phạm vi hoặc loại giá trị, để ngăn không cho các lỗi dữ liệu không mong muốn ngắt các phép tính.

Tạo một giá trị dựa trên một điều kiện

Bạn có thể sử dụng hàm IF được lồng để kiểm tra giá trị và tạo giá trị mới có điều kiện. Các chủ đề sau đây chứa một số ví dụ đơn giản về xử lý có điều kiện và các giá trị có điều kiện:

Kiểm tra lỗi trong một công thức

Không giống như Excel, bạn không thể có các giá trị hợp lệ trong một hàng của cột được tính toán và các giá trị không hợp lệ trong một hàng khác. Có nghĩa là nếu có lỗi trong bất kỳ phần nào của cột Power Pivot, toàn bộ cột được gắn cờ với lỗi, vì vậy bạn phải luôn sửa lỗi công thức dẫn đến các giá trị không hợp lệ.

Ví dụ, nếu bạn tạo một công thức chia cho bằng không, bạn có thể nhận được kết quả vô cực hoặc lỗi. Một số công thức cũng sẽ không thành công nếu hàm gặp một giá trị trống khi dự kiến một giá trị số. Mặc dù bạn đang phát triển mô hình dữ liệu của mình, tốt nhất là cho phép các lỗi xuất hiện để bạn có thể bấm vào thư và khắc phục sự cố. Tuy nhiên, khi bạn phát hành sổ làm việc, bạn nên kết hợp việc xử lý lỗi để ngăn các giá trị không mong muốn gây ra các phép tính không thành công.

Để tránh các lỗi trả về trong một cột được tính toán, bạn sử dụng kết hợp các hàm lô-gic và thông tin để kiểm tra lỗi và luôn trả về các giá trị hợp lệ. Các chủ đề sau đây cung cấp một số ví dụ đơn giản về cách thực hiện điều này trong DAX:

Tình huống: sử dụng thời gian thông minh

Hàm thời gian thông minh DAX bao gồm các hàm để giúp bạn truy xuất các ngày hoặc phạm vi ngày tháng từ dữ liệu của bạn. Sau đó, bạn có thể sử dụng những ngày hoặc phạm vi ngày tháng để tính toán giá trị trên các kỳ tương tự. Các hàm tình báo thời gian cũng bao gồm các hàm hoạt động với khoảng ngày chuẩn hóa, để cho phép bạn so sánh các giá trị trong các tháng, năm hoặc quý. Bạn cũng có thể tạo công thức so sánh các giá trị trong ngày đầu tiên và cuối cùng trong một khoảng thời gian đã xác định.

Để có danh sách tất cả các hàm thời gian thông minh, hãy xem hàm Time Intelligence (Dax). Để biết các mẹo về cách sử dụng ngày tháng và thời gian có hiệu quả trong phân tích Power Pivot, hãy xem ngày tháng trong Power Pivot.

Tính toán bán hàng tích lũy

Các chủ đề sau có chứa ví dụ về cách tính toán số dư đóng và mở. Các ví dụ cho phép bạn tạo cân bằng chạy qua các khoảng khác nhau, như ngày, tháng, quý hoặc năm.

So sánh các giá trị theo thời gian

Các chủ đề sau có chứa ví dụ về cách so sánh tổng trong các kỳ khác nhau. Các giai đoạn thời gian mặc định được hỗ trợ bởi DAX là tháng, quý và năm.

Tính giá trị trong phạm vi ngày tùy chỉnh

Xem các chủ đề sau đây để biết các ví dụ về cách truy xuất phạm vi ngày tùy chỉnh, chẳng hạn như 15 ngày đầu tiên sau khi bắt đầu một khuyến mãi bán hàng.

Nếu bạn sử dụng các hàm thời gian thông minh để truy xuất một tập hợp các ngày, bạn có thể sử dụng tập hợp ngày đó là đầu vào của một hàm thực hiện các phép tính, để tạo các tập hợp tùy chỉnh trong khoảng thời gian. Xem chủ đề sau đây để biết ví dụ về cách thực hiện như sau:

  • Hàm Parallelperiod

    Lưu ý: Nếu bạn không cần phải xác định một phạm vi ngày tùy chỉnh nhưng đang làm việc với các đơn vị kế toán tiêu chuẩn như tháng, quý hoặc năm, chúng tôi khuyên bạn nên thực hiện các phép tính bằng cách sử dụng các hàm tình báo thời gian được thiết kế cho mục đích này, chẳng hạn như TOTALQTD, TOTALMTD, TOTALQTD, v.v...

Tình huống: xếp hạng và so sánh các giá trị

Để chỉ hiện số n trên cùng trong một cột hoặc PivotTable, bạn có một vài tùy chọn:

  • Bạn có thể sử dụng các tính năng trong Excel 2010 để tạo bộ lọc trên cùng. Bạn cũng có thể chọn một số giá trị trên cùng hoặc dưới cùng trong PivotTable. Phần đầu tiên của phần này mô tả cách lọc cho 10 mục trên cùng trong PivotTable. Để biết thêm thông tin, hãy xem tài liệu hướng dẫn trong Excel.

  • Bạn có thể tạo công thức xếp hạng các giá trị động, rồi lọc theo giá trị xếp hạng, hoặc dùng giá trị xếp hạng làm slicer. Phần thứ hai của phần này mô tả cách tạo công thức này và sau đó dùng xếp hạng đó trong slicer.

Có những lợi thế và bất lợi cho từng phương pháp.

  • Bộ lọc trên cùng của Excel dễ sử dụng nhưng bộ lọc chỉ dành cho mục đích hiển thị. Nếu dữ liệu cơ bản thay đổi PivotTable, bạn phải làm mới theo cách thủ công PivotTable để xem các thay đổi. Nếu bạn cần làm việc động với bảng xếp hạng, bạn có thể sử dụng DAX để tạo công thức so sánh các giá trị cho các giá trị khác trong một cột.

  • Công thức DAX mạnh hơn; hơn thế nữa, bằng cách thêm giá trị xếp hạng vào slicer, bạn chỉ có thể bấm vào slicer để thay đổi số lượng giá trị trên cùng được hiển thị. Tuy nhiên, các phép tính sẽ tốn kém và phương pháp này có thể không phù hợp với các bảng có nhiều hàng.

Chỉ hiển thị mười mục hàng đầu trong PivotTable

Để hiển thị các giá trị trên cùng hoặc dưới cùng trong PivotTable

  1. Trong PivotTable, hãy bấm vào mũi tên xuống trong đầu đề nhãn hàng .

  2. Chọn bộ lọc giá trị> 10 trên cùng.

  3. Trong hộp thoại 10 <bộ lọc cột> , chọn cột để xếp hạng và số lượng giá trị như sau:

    1. Chọn trên cùng để xem các ô có giá trị cao nhất hoặc dưới cùng để xem các ô có giá trị thấp nhất.

    2. Nhập số giá trị trên cùng hoặc dưới cùng mà bạn muốn xem. Mặc định là 10.

    3. Chọn cách bạn muốn các giá trị được hiển thị:

Tên

Mô tả

Mục

Chọn tùy chọn này để lọc PivotTable để chỉ hiển thị danh sách các mục trên cùng hoặc dưới cùng theo giá trị của chúng.

Percent

Chọn tùy chọn này để lọc PivotTable để chỉ hiển thị các mục có thêm đến tỷ lệ phần trăm đã xác định.

Sum

Chọn tùy chọn này để hiển thị tổng các giá trị cho các mục trên cùng hoặc dưới cùng.

  1. Chọn cột có chứa các giá trị mà bạn muốn xếp hạng.

  2. Bấm vào OK.

Các mục đơn hàng động bằng cách sử dụng công thức

Chủ đề sau đây chứa một ví dụ về cách sử dụng DAX để tạo một thứ hạng được lưu trữ trong một cột được tính toán. Vì công thức DAX được tính toán tự động, bạn luôn có thể đảm bảo rằng xếp hạng là chính xác ngay cả khi dữ liệu cơ sở đã thay đổi. Ngoài ra, vì công thức được sử dụng trong một cột được tính toán, bạn có thể dùng xếp hạng trong slicer, rồi chọn Top 5, 10 hoặc thậm chí là các giá trị 100 trên cùng.

Lưu ý:  Trang này được dịch tự động nên có thể chứa các lỗi về ngữ pháp hoặc nội dung không chính xác. Mục đích của chúng tôi là khiến nội dung này trở nên hữu ích với bạn. Cho chúng tôi biết thông tin này có hữu ích hay không? Dưới đây là bài viết bằng tiếng Anh để bạn tham khảo..​

Phát triển kỹ năng Office 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.

×