Khi bắt đầu hành trình học Power Pivot và DAX, bạn có thể gặp nhiề khó khăn. Trong bài đăng này, UniTrain muốn chia sẻ đến bạn 3 mẹo ẩn Power Pivot trong Excel để giúp hành trình của bạn trở nên suôn sẻ hơn!
Mẹo Power Pivot số 1: Sao chép Data Model từ tệp này sang tệp khác một cách dễ dàng
Giả sử chúng ta đang làm việc với tệp Excel có cấu hình sau:
- Một loạt các bảng dữ liệu đã được đưa vào bằng cách sử dụng Power Query .
- Tạo Data Model với các mối quan hệ giữa các bảng.
- Tạo một loạt các DAX Measures và KPI để hỗ trợ tính toán.
Chúng ta muốn sao chép tất cả các khía cạnh này của tệp Excel và dán chúng vào tệp Excel mới để có thể thực hiện phân tích của mình theo một hướng khác.
Mẹo để chuyển toàn bộ mọi thứ là tạo một Pivot Table “giả” nhỏ.
Nếu tạo Bảng Pivot với ít nhất một trường từ mỗi bảng trong Data Model, chúng ta sẽ kết nối phân tích mọi phần tử của quá trình mô hình hóa.
Các bước thực hiện như sau:
- Bắt đầu một trang tính mới và chèn một Bảng Pivot mới bằng cách sử dụng dữ liệu từ Data Model làm nguồn.
- Chèn ít nhất 1 trường từ mỗi bảng của Data Model vào Pivot Table.
Pivot Table “giả” đã được tạo ra.
- Đánh dấu toàn bộ Pivot Table và nhấp vào Copy (CTRL-C).
- Bắt đầu một sổ làm việc mới và nhấp vào Paste (CTRL-V).
Mọi thứ từ sổ làm việc góp phần xây dựng nên Pivot Table ban đầu được sao chép được bao gồm trong sổ làm việc mới; truy vấn Power Query, Data Model, table relationships, Measures, KPIs,…
Mẹo số 2: Bảng đo lường chuyên dụng
Khi làm việc với các DAX Measures trong Power Pivot, các phép đo có thể được đặt trong bất kỳ bảng nào trong Data Model vì chúng là các phép tính độc lập.
Tùy chọn vị trí thay đổi từ người dùng này sang người dùng khác.
Một số người dùng thích đặt các phép đo trong bảng mà họ tập trung vào phân tích nhất, trong khi những người khác đặt tất cả các phép đo trong một bảng duy nhất.
Bảng mặc định để đặt tất cả các thước đo mới là bảng đã nhập Data Model trước.
Đích đến cho các phép đo này có thể được thay đổi trong quá trình tạo phép đo bằng cách nhấp vào menu thả xuống cho tùy chọn “Table Name” và chọn bảng mong muốn.
Nếu bạn đang đặt tất cả các phép đotrong một bảng duy nhất và bảng đó không phải là bảng đầu tiên được đặt trong Data Model, việc chọn lại điểm đến cho mỗi phép đo mới có thể trở nên nhàm chán.
Tạo bảng đo chuyên dụng
Tuy nhiên, một số người dùng muốn TẤT CẢ các phép đo được lưu trữ trong một “Bảng đo lường” chuyên dụng mà không có dữ liệu nào khác.
Đây là cách chúng ta có thể tạo một bảng như vậy.
LƯU Ý: Việc tạo bảng này phải được thực hiện TRƯỚC KHI đưa bất kỳ bảng nào khác vào Data Model.
- Bắt đầu một sổ làm việc mới và sao chép ô A1. (ô trống )
- Mở Data Model bằng cách chọn Data (tab) -> Data Tools (group) -> Data Model.
- Trong cửa sổ Power Pivot, bấm Paste.
- Trong cửa sổ Paste Preview, đặt tên cho bảng mới chẳng hạn như “KPI” và nhấp vào OK .
- Để tạo biểu tượng thước đo hấp dẫn trong Danh sách Trường Pivot Tables, hãy nhấp chuột phải vào tiêu đề của cột trống mới và chọn “Hide from Client Tools”.
- Đóng cửa sổ Power Pivot và nhập dữ liệu còn lại vào Data Model thông qua bất kỳ phương tiện nào bạn muốn (Power Query, nhập trực tiếp, v.v.)
Bây giờ, khi chúng ta tạo các phép đo, bảng đích mặc định cho các phép đo mới là bảng trống mà chúng ta đã tạo ở Bước 4 .
Khi bạn tạo Pivot Table từ Data Model, bạn sẽ thấy bảng Measures có biểu tượng SUM đẹp mắt bên cạnh tên bảng. Điều này được tạo ra khi chúng ta ẩn cột trống khỏi Client Tools.
Mẹo # 3: Sử dụng các Hàm Cube để làm báo cáo linh hoạt
Khi làm việc với Pivot Table, bạn có thể muốn tham chiếu riêng một giá trị cụ thể trong Pivot Table, có thể là một phần của dashboard report.
Một cách đơn giản để đạt được điều này là sử dụng hàm GETPIVOTDATA. Chỉ cần nhập một dấu bằng, sau đó nhấp vào ô của Pivot Table chứa giá trị mong muốn.
Kết quả như sau:
Điều này mang lại lợi ích lớn vì nếu bảng mở rộng, thu nhỏ hoặc thay đổi cấu trúc, miễn là trường [Quantity] vẫn còn trong Pivot Table, tổng hợp sẽ được trả về.
Mặc dù điều này có vẻ đơn giản và dễ dàng, nó vẫn có một một điểm yếu rất lớn.
Nếu trường tổng hợp của Pivot Table thay đổi ( ví dụ: từ [Quantity] thành [Median Quantity]), thì sử dụng chức năng GETPIVOTDATA không thành công.
Đây là lúc chúng ta cần đến chức năng CUBE. Chỉ cần làm theo các bước đơn giản:
- Tạo Pivot Table chứa các giá trị bạn muốn trong dashboard.
- Với Pivot Table mới được chọn, hãy chuyển đổi Pivot Table thành một tập hợp các hàm độc lập bằng cách chọn Pivot Table Analyze (tab) -> Calculations (group) -> OLAP Tools -> Convert to Formulas.
- Hai ô tạo nên Pivot Table đã được thay thế bằng các hàm CUBE.
Trong ví dụ trên, vì hàm CUBE trong ô D5 phụ thuộc vào kết quả của hàm CUBE trong ô D4 , chúng ta không thể chỉ cần sao chép công thức D5 và loại bỏ công thức D4 .
Tuy nhiên, chúng ta có thể kết hợp hai hàm thành một hàm duy nhất bằng cách đặt toàn bộ hàm CUBEMEMBER thay cho tham chiếu ô D4 trong hàm CUBEVALUE.
Nếu bạn đã quen với các hàm CUBE, bạn sẽ biết rằng chúng ta có thể rút ngắn hàm bằng cách loại bỏ phần “CUBEMEMBER (“ThisWorkbookDataModel”,” của công thức mà không gặp bất kỳ vấn đề nào.
Kết quả của hàm CUBE sẽ làm mới bất cứ khi nào Data Model được làm mới.
Tùy chỉnh Định dạng Kết quả CUBE
Để làm cho đầu ra của hàm CUBE dễ nhìn hơn, chúng ta có thể ghép một nhãn vào phía trước hàm CUBEVALUE .
Chúng ta cũng có thể sử dụng hàm TEXT để áp dụng định dạng công thức cho kết quả của hàm CUBEVALUE .
Nguồn: xelplus
Xem thêm
Mới: Phân tích Doanh số sản phẩm bán chạy sử dụng Pivot Table % Running Total
5 điều thú vị bạn có thể làm với Power Pivot trong Excel