Power BI Lab 6: Create DAX Calculations in PBI Desktop Part 2

Lab story

1. Prepare Data in Power BI Desktop
2. Load Data in Power BI Desktop
3. Model Data in Power BI Desktop, Part 1
4. Model Data in Power BI Desktop, Part 2
5. Create DAX Calculations in Power BI Desktop, Part 1
6. Create DAX Calculations in Power BI Desktop, Part 2
7. Design a Report in Power BI Desktop, Part 1
8. Design a Report in Power BI Desktop, Part 2
9. Create a Power BI Dashboard
10. Create a Power BI Paginated Report
11. Perform Data Analysis in Power BI Desktop
12. Enforce Row-Level Security

Exercise 1: Làm việc với Filter Context trong DAX

Task 1: Tạo biểu đồ ma trận

1. Trong Power BI Desktop, Report view, tạo một trang báo cáo mới.
2. Trên Page 3, thêm một biểu đồ dạng ma trận vào trang báo cáo
3. Kéo toàn bộ cây phân cấp Region | Regions và thả vào bên trong biểu đồ ma trận.
4. Thêm trường vào biểu đồ Sales | Sales.
5. Để mở rộng toàn bộ cây phân cấp, nhấn 2 lần vào biểu tượng mũi tên rẽ nhánh ở góc trên bên phải biểu đồ. Hãy nhớ lại rằng trong buổi thực hành từ trước, cây phân cấp Regions có 3 cấp bậc là Group, Country và Region. 
6. Để đặt định dạng, trong ngăn Visualizations chọn ngăn Format.
7. Trong hộp Search, nhập vào Stepped.
8. Đặt thuộc tính Stepped Layout là Off.
9. Hãy chắc chắn rằng ma trận của bạn bây giờ có 4 cột như sau. Ở Adventure Works, khu vực bán hàng (sales region) được chia thành các nhóm (group), nhóm gồm các quốc gia (country), quốc gia bao gồm các vùng miền (region). Ngoại trừ Mỹ (US), các nước còn lại đều chỉ có 1 vùng, tên vùng được đặt trung với tên của quốc gia đó. Riêng với Mỹ được chia thành 5 khu vực địa lý khác nhau.

Task 3: Kiểm soát filter context

1. Thêm một measure vào bảng Sales theo công thức sau:

    Sales All Region =
   
    CALCULATE(SUM(Sales[Sales]), REMOVEFILTERS(Region))
   

   Hàm CALCULATE() là một hàm cực kỳ “mạnh” trong việc kiểm soát filter context. Hàm CALCULATE() có thể có 1 hoặc nhiều đối số (argument). Đối số đầu tiên là một câu lệnh, phép tính hoặc một measure, các đối số sau đó thể hiện bộ lọc được điều chỉnh và áp dụng trong công thức. Hàm REMOVEFILTERS() sẽ bỏ đi tất cả các bộ lọc đang hoạt động. Hàm này có thể không có đối số nào, nhưng cũng có thể có đối số là một bảng, một cột hoặc nhiều cột. Trong công thức trên, bạn đang tính tổng các giá trị trong cột Sales trong một bộ lọc đã được điều chỉnh, đó là bỉ đi tất cả các bộ lọc được áp dụng cho cột Region. 
2. Để quan sát kết quả một cách rõ hơn, thêm measure Sales All Region vào biểu đồ ma trận. Bạn sẽ thấy công thức này trả về tổng sales cho tất cả các khu vực, quốc gia, vùng miền như sau: Việc tính ra tổng doanh thu của tất cả các khu vực bán hàng như vậy sẽ giúp bạn tính toán được tỷ lệ phần trăm đóng góp (percent of total) của từng khu vực vào tổng, bằng cách chia Sales của từng khu vực cho Sales tổng (ở đây là measure Sales All Region)
3. Bây giờ, click chọn lại measre Sales All Region trong ngăn Field và thay measure đó bằng công thức như sau:

    Sales % All Region =  
    ‎DIVIDE(  
    ‎ SUM(Sales[Sales]),  
    ‎ CALCULATE(  
    ‎ SUM(Sales[Sales]),  
    ‎ REMOVEFILTERS(Region)  
    ‎ )  
    ‎)
   

   Measure của bạn đã được đổi tên để thể hiện đúng ý nghĩa của measure. Hàm DIVIDE() sẽ chia measure Sales (không bị điều chỉnh bởi filter context) cho measure Sales đã được điều chỉnh trong filter context. 
4. Trong biểu đồ ma trận, chú ý rằng measure của bạn đã được đổi tên và bây giờ đang chứa các giá trị khác nhau cho từng group, country, region.
5. Sửa lại định dạng measure Sales % All Region thành phần trăm với 2 chứ số sau dấu thập phân
6. Trong biểu đồ ma trận, xem lại giá trị của measure Sales % All Region.
7. Thêm một measure DAX khác vào bảng Sales và đặt định dạng là phần trăm như sau:

    Sales % Country =  
    ‎DIVIDE(  
    ‎ SUM(Sales[Sales]),  
    ‎ CALCULATE(  
    ‎ SUM(Sales[Sales]),  
    ‎ REMOVEFILTERS(Region[Region])  
    ‎ )  
    ‎)
   

8. Chú ý rằng measure Sales % Country hơi khác một chút với measure Sales % All Region.Sự khác biệt nằm ở số chia trong cong thức đã được điều chỉnh bởi việc bỏ đi các bộ lọc trong cột Region trong bảng Region chứ không phải xóa bộ lọc cho tất cả các cột trong bảng Region. Điều này nghĩa là mọi bộ lọc được áp dụng cho group và country vẫn được giữ nguyên. Kết quả của measure sẽ thể hiện % sales cho từng country.
9. Thêm measure Sales % Country vào biểu đồ ma trận.
10. Chú ý rằng chỉ United States đang thể hiện các giá trị nhỏ hơn 100% cho mỗi region. Điều này là bởi vì chỉ có Mỹ có nhiều region, các quốc gia còn lại đều chỉ chứa một region duy nhất, dẫn đến giá trị hiển thị cho mỗi region cho quốc gia đó là 100%.
11. Bạn có thể làm công thức DAX của bạn tốt hơn nữa bằng cách sửa lại công thức của measure Sales % Country như sau.

    Sales % Country =  
    ‎IF(  
    ‎ ISINSCOPE(Region[Region]),  
    ‎ DIVIDE(  
    ‎ SUM(Sales[Sales]),  
    ‎ CALCULATE(  
    ‎ SUM(Sales[Sales]),  
    ‎ REMOVEFILTERS(Region[Region])  
    ‎ )  
    ‎ )  
    ‎)
    

    Hàm ISINSCOPE bên trong hàm IF được sử dụng khi muốn kiểm tra xem một cột region là một cấp bậc trong cây phân cấp. Nếu đúng, hàm DIVIDE() sẽ được thực thi. Vế false trong hàm IF() đang bị thiếu, nghĩa là nếu sai thì hàm sẽ trả về giá trị blank. 
12. Notice that the Sales % Country measure now only returns a value when a region is in scope.
13. Thêm 1 measure DAX cho bảng Sales như sau và đặt định dạng là phần trăm (percentage), sau đó thêm vào biểu đồ ma trận.

    Sales % Group =  
    ‎DIVIDE(  
    ‎ SUM(Sales[Sales]),  
    ‎ CALCULATE(  
    ‎ SUM(Sales[Sales]),  
    ‎ REMOVEFILTERS(  
    ‎ Region[Region],  
    ‎ Region[Country]  
    ‎ )  
    ‎ )  
    ‎)
    

14. Một cách khác để viết công thức DAX cho measure Sales % Group là:

    Sales % Group =  
    ‎IF(  
    ‎ ISINSCOPE(Region[Region])  
    ‎ || ISINSCOPE(Region[Country]),  
    ‎ DIVIDE(  
    ‎ SUM(Sales[Sales]),  
    ‎ CALCULATE(  
    ‎ SUM(Sales[Sales]),  
    ‎ REMOVEFILTERS(  
    ‎ Region[Region],  
    ‎ Region[Country]  
    ‎ )  
    ‎ )  
    ‎ )  
    ‎)
    

15. Trong Model view, lưu 3 measure mới vào chung một folder tên là Ratios.
16. Lưu file Power BI Desktop.

Exercise 2: Làm việc với Time Intelligence

Task 1: Tạo measure DAX YTD

1. Trong Report view, trên Page 2, chú ý răng biểu đồ ma trận đang thể hiện nhiều measures cho năm và tháng được gộp thành nhóm theo các dòng.
2. Thêm measure DAX vào bảng Sales và đặt định dạng là 0 chữ số thập phân như sau:

    Sales YTD =  
    ‎TOTALYTD(SUM(Sales[Sales]), 'Date'[Date], "6-30")
   

   Hàm TOTALYTD() sẽ thực hiện phép tính – trong trường hợp này là tổng Sales – cho một cột Date được cho trước. Cột Date này bắt buộc phải thuộc về một bảng DATE được đánh dấu là bảng Date (date table) – cách đánh dấu một bảng là bảng date chúng ta đã học ở lab trước: Create DAX Calculations in Power BI Desktop, Part 1 lab.Hàm còn có thể có thêm (hoặc không) một đối số thứ ba thể hiện ngày cuối cùng của năm. Khi trong công thức không có phần đối số thứ ba, nghĩa là ngày cuối cùng của năm là ngày 31 tháng 12. Đối với Adventure Works, tháng 6 là tháng cuối cùng của năm, do đó 30/6 là ngày cuối cùng của năm và được thể hiện trong công thức là “6-30”. 
3. Thêm trường Sales và meaure Sales YTD biểu đồ ma trận. Kết quả cho thấy measure Sales YTD trả về giá trị tích lũy của doanh sô từ đầu năm cho đến ngày được tính. Hàm TOTALYTD() cũng thực hiện kiểm soát bộ lọc về thời gian. Ví dụ, để tính sales YTD cho tháng 9 năm 2017 (tháng thứ 3 trong năm tài chính), trong bảng Date, mọi bộ lọc đã được bỏ đi và thay bằng bộ lọc ngày mới bắt đầu từ ngày đầu tiên của năm tài chính 2017 (1 tháng 7 năm 2017) và mở rộng đến ngày cuối cùng trong giai đoạn được xét (30 tháng 9 năm 2017). function performs filter manipulation, specifically time filter manipulation. Ngoài ra, DAX có rất nhiều hàm Time Intelligence khác để kiểm soát các loại bộ lọc khác nhau. 

Task 2: Tạo measure DAX YoY growth

1. Thêm vào bảng Sales một measure mới như sau:

    Sales YoY Growth =  
    ‎VAR SalesPriorYear =  
    ‎ CALCULATE(  
    ‎ SUM(Sales[Sales]),  
    ‎ PARALLELPERIOD(  
    ‎ 'Date'[Date],  
    ‎ -12,  
    ‎ MONTH  
    ‎ )  
    ‎ )  
    ‎RETURN  
    ‎ SalesPriorYear
   

   Công thức tính Sales YoY Growth có mệnh đề VAR – khai báo biến. Biến (variable) được sử dụng phổ biến để làm đơn giản hóa logic của công thức, đồng thời hiệu quả hơn trong trường hợp một phần công thức cần được dùng đi dùng lại trong toàn bộ phép tính. Các biến được khai báo phải có tên khác nhau, kết quả của phép tính được thể hiện sau mệnh đề với keyword là RETURN. Trong công thức trên, biến SalesPriorYear được gán cho phép tính tổng cho cột Sales khi có bộ lọc là hàm PARALLELPERIOD() – hàm này lấy ra giá trị ngày cách ngày được lọc là 12 tháng. 
2. Thêm measure Sales YoY Growth vào biểu đồ ma trận
3. Chú ý rằng measure này sẽ trả về giá trị BLANK cho 12 tháng đầu tiên bởi vì 12 tháng này không có PARALLEL PERIOD ở trước nó 12 tháng (không có giá trị sales trước năm tài chính 2017).
4. Chú ý rằng giá trị cho Sales YoY Growth cho 2018 Jul chính là giá trị Sales cho 2017 Jul. Bây giờ, sau khi đã tìm ra được Sales của năm trước, chúng ta sẽ tạo công thức để tính ra tỷ lệ % tăng trưởng sales. 
5. Để hoàn thành measure, ghi đè lên công thức cũ bằng công thức measure mới là Sales YoY Growth và đặt định dạng measure là phần trăm với hai chữ số thập phân như sau:

    Sales YoY Growth =  
    ‎VAR SalesPriorYear =  
    ‎ CALCULATE(  
    ‎ SUM(Sales[Sales]),  
    ‎ PARALLELPERIOD(  
    ‎ 'Date'[Date],  
    ‎ -12,  
    ‎ MONTH  
    ‎ )  
    ‎ )  
    ‎RETURN  
    ‎ DIVIDE(  
    ‎ (SUM(Sales[Sales]) - SalesPriorYear),  
    ‎ SalesPriorYear  
    ‎ )
   

6. Kiểm tra lại để chắc chắn rằng Sales YoY Growth 2018 Jul là 392.83%. Điều này có nghĩa là doanh số tháng 7 năm 2018 ($2,411,559) cao hơn cùng kỳ năm trước ($489,328) là 392.83%. 
7. Trong Model view, chuyển 2 measure mới tạo vào chung một folder tên là Time Intelligence.