Power BI

Ngày tạo 11/09/2023

- 1.164 Lượt xem

Ngày hôm nay mình có nhận được 1 bài toán như sau.

Giám đốc yêu cầu tạo ra 1 Dashboard trên đó có 2 mốc thời gian được tạo thành 2 Slicer.

Giờ giám đốc muốn là viết công thức DAX để có thể tính toán được doanh số trên 2 slicer riêng biệt. Sau đó sẽ so sánh doanh số của các mốc thời gian đó.

Bài toán nghe có vẻ lạ nhưng các bạn đã từng bắt gặp các bài toán dễ hơn trước kia.

Ví dụ so sánh doanh thu tháng hiện tại với tháng trước đó hoặc thời điểm hiện tại với năm trước đó chẳng hạn. Với bài toán này bạn có thể dễ dàng hoàn thành nó bằng các hàm Time Intelligence.

Nhưng với bài toán trên thì bạn sẽ phải làm rất khác.

Thứ 1, bạn cần có 2 bảng Date Table.

Thứ 2, bạn nhất định phải viết các hàm DAX để có thể tính toán được sự chênh lệch doanh số giữa các mốc thời gian này.

Trong bài viết này mình sẽ hướng dẫn bạn cách làm thế nào để có thể giải quyết bài toán này nhé.

Chúng ta sẽ có 1 Data Model dạng Star Schema đơn giản như sau.

Để xử lý bài toán trên thì chúng ta cần tạo ra 1 bảng d_Date nữa giống y hệt và đặt tên là Comparison Date.

Để làm điều này thì chúng ta sẽ vào Power Query và Duplicate bảng d_Date ra.

Sau khi duplicate thì ta sẽ đổi tên cột Date của bảng d_Comparison_Date thành ComparisonDate

Sau khi load dữ liệu vào Data Model thì ta sẽ có Model trông như sau:

Với bảng d_Comparison_Date thì ta sẽ kết nối với bảng d_Date thông qua cột Date và ComparisonDate của 2 bảng (Lưu ý là mối quan hệ giữa d_Comparison_Date với d_Date sẽ là 1 Inactive Relationship)

Bạn có thể tham khảo cách setup như bảng sau.

Tiếp theo bạn chuyển qua Page Report View và chúng ta sẽ setup 1 Matrix và Slicer đơn giản như sau.

Lưu ý là mốc thời gian Date và Comparison Date bạn có thể tuỳ chỉnh theo ý mình nhé.

Tiếp tục bạn sẽ viết 1 hàm DAX để tính toán Sales Amount đơn giản như sau:

Sales Amount =

SUMX(

f_Sales,

f_Sales[Quantity] * f_Sales[Net Price]

)

Và đây là kết quả sau khi bạn kéo Measure trên vào bảng Matrix.

Giờ bạn sẽ cần tính toán được Sales Amount cho khoảng thời gian được setup bên Comparison Date.

Bạn sẽ viết 1 hàm DAX như sau.

Comparison Sales Amount =

VAR Comparison_Date_Rng =

CALCULATETABLE(

VALUES(d_Date[Date]),

ALL(d_Date),

USERELATIONSHIP(d_Date[Date], d_Comparison_Date[ComparisonDate])

)

VAR Result =

CALCULATE(

[Sales Amount],

Comparison_Date_Rng

)

RETURN

Result

Và dưới đây là kết quả.

Mình sẽ giải thích thêm về công thức trên 1 chút để các bạn có thể hiểu rõ hơn nhé.

Chúng ta sẽ tập trung vào công thức trên đây trước.

VAR Comparison_Date_Rng =

CALCULATETABLE(

VALUES(d_Date[Date]),

ALL(d_Date),

USERELATIONSHIP(d_Date[Date], d_Comparison_Date[ComparisonDate])

)

Thường thì các bạn rất quen thuộc với hàm CALCULATE. Tuy nhiên CALCULATETABLE thì không.

Về cơ bản thì CALCULATETABLE là 1 hàm Table Function.

Tham số đầu tiên của nó thay vì Expression (Phép tính) như CALCULATE thì bạn đưa vào 1 Table.

Còn lại các tham số sau thì đều là FILTER như CALCULATE mà thôi.

Đầu tiên ta đưa vào VALUES(d_Date[Date]).

Việc làm này có ý nghĩa là bạn lấy ra các giá trị không trùng nhau trong cột Date của bảng d_Date.

Tiếp đó ALL(d_Date) có nghĩa là bạn sẽ loại bỏ hoàn toàn các Filter Context đang áp dụng lên bảng d_Date.

Như các bạn thấy thì trên Dashboard của chúng ta đang áp dụng 1 Filter Context lên bảng d_Date đó là Date sẽ nằm trong khoảng từ 1/1/2009 đến 30/06/2009. Bạn cứ hiểu như là ALL(d_Date) sẽ giúp bạn Select All toàn bộ giá trị trong cột Date bảng d_Date vậy.

Vậy hàm USERELATIONSHIP(d_Date[Date], d_Comparison_Date[ComparisonDate]) nghĩa là gì?

Bạn có nhớ là mình đang thiết lập mối quan hệ giữa bảng d_Date và d_Comparison_Date theo 1 mối quan hệ Inactive hay không?

Hàm USERELATIONSHIP này sẽ giúp chúng ta Active mối quan hệ giữa 2 cột này lên.

Và khi đó thì đường từ bảng ComparisonDate sẽ thông thẳng tới bảng Sales. Qua đó giúp chúng ta Filter ra các dữ liệu Sales tương ứng với khoảng thời gian trên Slicer mà bạn chọn.

Tiếp theo đó thì hàm

VAR Result =

CALCULATE(

[Sales Amount],

Comparison_Date_Rng

)

Sẽ sử dụng bảng Comparison_Date_Rng bạn vừa tạo ra để làm bảng Filter tính Sales Amount.

Khi đó bạn sẽ tính ra tổng Sales Amount theo Product Brand và khoảng thời gian Comparison Date mà bạn đã chọn.

Tuy nhiên đến đây mình vẫn chưa ưng về kết quả lắm. Vì lúc này 2 khoảng thời gian có số ngày chênh nhau rất nhiều. Ví dụ như Date thì bạn có 6 tháng, còn ComparisonDate thì bạn lại tính đến 12 tháng.

Vì thế hiện tại mình muốn là mốc Date bạn chọn bao nhiêu ngày thì mốc Comparison Date cũng chỉ tính bấy nhiêu ngày thôi.

Cách tính của chúng ta sẽ như sau.

Ví dụ mốc 1 có 100 ngày, mốc 2 có 300 ngày.

Khi đó để tính ra kết quả ta sẽ cần tính như sau.

Lấy tổng Sales Amount của mốc 2 / 300 ngày để tính doanh thu trung bình theo ngày của mốc 2.

Sau đó nhân với số ngày của mốc 1 để thu được kết quả cuối cùng.

Để làm được điều đó thì ta sẽ viết 1 hàm DAX nữa như sau.

Comparison Sales Amount Advanced =

VAR Current_Date_Period =

VALUES(d_Date[Date])

VAR Comparison_Date_Period =

CALCULATETABLE(