Tin Nóng
Trong các kỹ thuật về phân tích dữ liệu, chúng ta thường gặp phải yêu cầu So sánh về tỷ lệ tăng trưởng của năm sau so với năm trước. Trong bài viết này, chúng ta hãy cùng Học Excel Online tìm hiểu kỹ thuật phân tích này trong Power BI nhé. Để dễ hình dung về dạng bài này, chúng ta cùng làm việc với 1 ví dụ cụ thể là: tính chỉ số tăng trưởng theo quý, kết quả dạng %.
Bài tập yêu cầu tính chỉ số tăng trưởng theo quý
Trước hết các bạn có thể tải file tài liệu mẫu tại địa chỉ:
Giao diện bài tập của chúng ta như sau:
Bảng dữ liệu
Nguyên tắc tính
Việc xác định rõ nguyên tắc tính là rất quan trọng. Bởi nếu không có nguyên tắc rõ ràng từ đầu, chúng ta không thể biết sẽ sử dụng công thức nào để tính ra kết quả.
Dữ liệu nguồn là trên file Excel. Dữ liệu này có thể trích xuất từ phần mềm khác ra, hoặc do người quản lý công việc nhập thủ công vào. Yêu cầu là khi dữ liệu được thêm vào sẽ tự động ra báo cáo tình hình tăng trưởng theo quý của các mã trong cột Ticker.
Các bước thực hiện tính chỉ số tăng trưởng theo quý trên Power BI
Nạp dữ liệu từ file Excel vào Power BI
Để dễ dàng nạp dữ liệu từ Excel vào Power BI, bạn có thể thực hiện các bước sau:
- Bước 1: Đưa bảng dữ liệu về dạng Table (Phím tắt Ctrl + T). Mục đích của việc này là để đảm bảo nội dung cần đưa vào Power BI sẽ chỉ trong phạm vi Table đã được xác định, không lấy thừa các nội dung khác.
- Bước 2: Khi đổi sang dạng Table, bạn nên đặt tên cho bảng này để tránh nhầm lẫn với các bảng khác (nếu có). Sau đó bạn có thể đóng file lại.
- Bước 3: Vào Power BI, bạn chọn mục Edit Queries.
- Bước 4: Trong Edit Queries, chọn New Source > Excel > Chọn tới file Excel đã lưu ở bước 2.
- Bước 5: Trong giao diện cửa sổ lấy nội dung trong file Excel, bạn chọn tên của Table đã đặt ở bước 2.
Tham khảo bài viết: Hướng dẫn cách nạp dữ liệu từ Excel vào Power BI
Kết quả là nội dung trong File Excel sẽ được đưa vào Power BI với 1 Query mới. Giả sử chúng ta đặt tên cho Query này là MyData.
Thêm bảng dữ liệu ngày tháng
Không nên sử dụng trực tiếp dữ liệu trong bảng đã có. Bạn nên tạo thêm 1 bảng dữ liệu ngày tháng riêng. Mục đích của việc này là:
- Bảng dữ liệu ban đầu có thể không liên tục về thời gian.
- Một số hàm DAX yêu cầu tính toán với điều kiện thời gian liên tục.
Do đó chúng ta nên có 1 bảng dữ kiện ngày tháng, thời gian riêng, liên tục theo ngày để đảm bảo có thể dùng được các hàm tính toán trong Power BI, đặc biệt là với các hàm thuộc nhóm Time Intelligence.
Cách làm như sau:
- Bước 1: Bạn tải về máy tính đoạn code sau:
- Bước 2: Tạo 1 Query mới, trong Query này bạn bấm vào mục Advanced Editor và dán đoạn code vào. Sau đó bấm OK
- Bước 3: Đoạn code sẽ tạo ra 1 bảng yêu cầu nhập tham số. Đây chính là phạm vi thời gian bạn muốn tính. Dựa theo thời gian có trong bảng dữ liệu MyData. Nhập StartDate là ngày đầu năm của năm nhỏ nhất, EndDate là ngày cuối năm của năm lớn nhất. FYStartMonth có thể nhập số bất kỳ. Sau đó bấm vào nút Invoke để khởi tạo bảng thời gian.
- Bước 4: Bảng Invoked Function được tạo ra. Lưu bảng này với tên DateTable.
Như vậy là bước làm việc với Query đã hoàn thành. Mục đích của việc này là giúp bạn có 1 hệ thống dữ liệu phù hợp giúp tính toán được kết quả. Bạn có thể bấm Close & Apply để hoàn tất bước này.
Tạo mối quan hệ giữa 2 bảng
Để có thể giúp các hàm tính toán làm việc đúng (tính 1 chỉ tiêu ở bảng MyData với 1 điều kiện ở bảng DataTable) thì bạn cần phải chỉ ra được mối quan hệ giữa 2 bảng này: Có liên quan với nhau ở chỗ nào, thông qua cột nào.
Trong cửa sổ Power BI, bạn bấm vào mục Relationship để thiết lập mối quan hệ giữa các bảng:
- Cột chứa dữ liệu có liên quan: Cột Ngày
- Dạng quan hệ: 1 ngày trong bảng DateTable có thể xuất hiện nhiều lần trong bảng MyData (giả sử dữ liệu có thể nhiều hơn nữa, thì thời gian sẽ lặp đi lặp lại, do đó 1 ngày có thể xuất hiện nhiều lần trong bảng MyDat).
Như vậy ta có thể có mối quan hệ dạng *:1 (Many to One) như sau:
Xây dựng các công thức tính
Sau khi đã hoàn thành các bước trên, chúng ta bắt đầu thực hiện việc tính toán để tìm ra kết quả.
Các bước tính toán gồm:
1. Mục tiêu: % tăng trưởng theo quý. Để tính được chỉ tiêu này cần tính được:
- Tổng Assets của từng quý.
- Tổng Assets của quý đó cùng kỳ năm trước.
- Từ 2 chỉ tiêu trên tính chênh lệch bằng phép trừ
- Để tính ra tỷ lệ % thì dùng phép chia. Tuy nhiên không dùng được dấu / mà phải dùng hàm DIVIDE để thực hiện phép chia.
2. Tổng Assets của từng quý: tính tổng Assets kèm theo điều kiện là theo quý
3. Tổng Assets của quý đó cùng kỳ năm trước: tính lại cho tổng Assets của từng quý, nhưng theo cùng kỳ năm trước.
Với yêu cầu này chúng ta sẽ phải tính lại bước 2, trong đó kết hợp với hàm SAMEPERIODLASTYEAR
Để thực hiện các phép tính (Measures), chúng ta tạo 1 bảng riêng với chức năng Enter Data, đặt tên là MyMeasures. Trong đó tạo các Measure lần lượt như sau:
1. Tổng Assets:
Total Assets = SUM( MyData[Assets] )
2. Tổng Assets theo quý
Total Assets QTD = CALCULATE( [Total Assets], DATESQTD( DateTable[Date] ))
Chú ý trong bước này hàm DATESQTD lấy theo cột Date trong bảng DateTable để đảm bảo thời gian liên tục
3. Tổng Assets theo quý cùng kỳ năm trước
Total Assets QTD Last Year = CALCULATE( [Total Assets QTD], SAMEPERIODLASTYEAR( DateTable[Date] ))
Chú ý trong bước này hàm SAMEPERIODLASTYEAR lấy theo cột Date trong bảng DateTable để đảm bảo thời gian liên tục
4. Chênh lệch giữa quý hiện tại với quý cùng kỳ năm trước:
Chenh lech = [Total Assets QTD] – [Total Assets QTD Last Year]
5. % Chênh lệch
% chenh lech = DIVIDE( [Chenh lech], [Total Assets QTD Last Year], 0)
Kết quả
Sau khi đã tính ra các chỉ tiêu, chúng ta có thể biểu diễn kết quả trên màn hình Visualization như sau:
