Power BI DAX: Cách sử dụng hàm CALCULATE kèm ví dụ cụ thể

Ngày đăng: 28/02/2024

Hàm CALCULATE trong Power BI là một hàm phổ biến và quan trọng. Hãy cùng Datapot tìm hiểu về cách sử dụng cũng như vận dụng hàm này vào các ví dụ thực tế nhé.

CALCULATE là hàm rất phổ biến và khá quan trọng trong DAX. Việc hiểu và vận dụng thành thạo được hàm này sẽ giúp các bạn có được nền tảng tốt và chủ động hơn trong việc xây dựng các công thức DAX phức tạp sau này.

Trong bài viết này Datapot sẽ hướng dẫn các bạn cách sử dụng hàm CALCULATE và vận dụng nó trong một số tình huống phổ biến.

Video hướng dẫn thực hành


Mục đích sử dụng của hàm

Đầu tiên, hàm CALCULATE được dùng để đánh giá, tính toán một biểu thức (expression) trong ngữ cảnh được sửa đổi bởi các bộ lọc (filter).

Hàm này sẽ trả về kết quả của biểu thức được đánh giá, tính toán theo ngữ cảnh được sửa đổi bởi bộ lọc.

Cú pháp của hàm CALCULATE

Thông số (Parameter)

Mô tả

Biểu thức (Expression)

Biểu thức cần đánh giá, tính toán (measure hoặc DAX formula)

Bộ lọc (Filter)

Kiểu True/False, giá trị cố định hay bảng được xác định bởi bộ lọc

Giới thiệu Dataset thực hành

Trong phần tiếp theo của bài viết, các ứng dụng của hàm CALCULATE, dataset được sử dụng là AventureWorkDW2020

Với bộ dữ liệu này, chúng ta có mô hình dữ liệu theo Star Schema như hình bên dưới.

Trong đó Sales là bảng Fact, và được kết nối tới các bảng Dim sau:

  • Date
  • Customer
  • Product
  • Reseller
  • Sales Territory

Ngoài ra, một bảng Metrics được tạo sẵn để chứa và tổ chức các measures hiệu quả. Với dataset này chúng ta sẽ thực hành hàm CALCULATE với các tính toán liên quan đến doanh số.

Các ứng dụng của hàm CALCULATE

Sử dụng hàm CALCULATE với giá trị lọc cố định

Đầu tiên, chúng ta hãy tạo measure tính Revenue:

Revenue = SUM(Sales[Sales Amount])

Với measure này chúng ta có tổng Revenue và Revenue tương ứng với từng Category như sau:

Trong trường hợp, chúng ta chỉ muốn tính Revenue của chỉ một Category, ví dụ như Bikes, ta sẽ viết measure như sau:

Revenue bikes = CALCULATE([Revenue], 'Product'[Category] = "Bikes")
  • [Revenue]: measure
  • ‘Product'[Category] = “Bikes”: điều kiện lọc Category là Bikes

Chúng ta có thể tạo một card visual cho Revenue bikes để đối chiếu kết quả ở bảng, ta sẽ thấy hai kết quả bằng nhau.

Ví dụ này thường được dụng nhiều trong trường hợp:

  • Bảng chứa nhiều trường, chỉ số và cần thêm chỉ số nhưng lọc theo một điều kiện cụ thể nào đó
  • Hoặc thể hiện chỉ số lọc theo nhóm, danh mục bằng Card visual.

Loại bỏ bộ lọc bằng hàm CALCULATE

Với bảng trong ví dụ trên, nếu chúng ta muốn có một cột chỉ thể hiện tổng Revenue của tất cả Category thì làm thế nào?

Khi kéo measure Revenue vào bảng, thì Revenue sẽ luôn được tính và lọc theo các Category, nhưng chúng ta hoàn toàn có thể loại bỏ bộ lọc này với hàm CALCULATE bằng measure sau:

Revenue All Category = CALCULATE([Revenue], ALL('Product'[Category]))

ALL(‘Product'[Category]): luôn lấy tất cả Category

Tím hiểu thêm về hàm ALL

Measure Revenue All Category sẽ luôn thực hiện với tất cả các Category, ngày cả khi nếu ta có 1 slicer để chọn theo Category như trong hình bên dưới. Trong khi đó, measure Revenue sẽ bị thay đổi theo slicer.

Ngoài hàm All, ta cũng có thể sử dụng hàm REMOVEFILTERS, tức thay vì lấy tất, ta sẽ loại bỏ bộ lọc theo Category.

Revenue All Category = CALCULATE([Revenue], REMOVEFILTERS('Product'[Category]))

Tìm hiểu thêm về hàm REMOVEFILTERS

Các bạn hãy thử nhé!

Từ ví dụ trên, vì Revenue All Category không bị ảnh hưởng bởi bộ lọc, ta hoàn toàn có thể tính thêm chỉ số phần trăm Revenue của các Category với measure như sau:

% Rev by Cat = DIVIDE([Revenue], [Revenue All Category])

Tìm hiểu thêm về hàm DIVIDE

Kết hợp hàm Time Intelligence với hàm CALCULATE

Ứng dụng tiếp theo là CALCULATE kết hợp với nhóm hàm Time Intelligence, mục đích là để tính toán các chỉ tiêu theo thời gian như: so sánh cùng kỳ, tăng trưởng so với cùng kỳ, luỹ kế…. Phân tích hoạt động kinh doanh theo chiều thời gian là yếu tố tất cả các doanh nghiệp đều cần, nên ứng dụng này cực kì quan trọng.

Với ứng dụng này, chúng ta sẽ có một bảng gồm Revenue theo Month

Và chúng ta sẽ thực hiện tính toán các chỉ số sau:

  • Tính doanh số cùng kỳ
  • Tính doanh số lũy tiến theo năm
  • Tính % doanh số thay đổi theo năm

Tính doanh số cùng kỳ năm trước

Để tính được doanh số cùng kỳ năm trước, ta viết measure như sau:

Revenue SP LY = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
  • SAMEPERIODLASTYEAR(‘Date'[Date]): trả về khoảng thời gian cùng kỳ của năm trước đó

Tìm hiểu thêm về hàm SAMEPERIODLASTYEAR

Sau đó kéo measure vào bảng ta được kết quả như hình dưới.

Chúng ta sẽ thấy, measure trả về kết quả là Revenue của tháng đó nhưng lùi lại 1 năm. Ví dụ như tháng 7 (Jul) của các năm 2017, 2018, 2019.

Tính doanh số lũy kế theo năm

Để tính doanh số lũy kế đến ngày hiện tại theo năm, ta sử dụng measure sau:

Revenue YTD = CALCULATE([Revenue], DATESYTD('Date'[Date]))
  • DATESYTD(‘Date'[Date]): chỉ định thời gian lũy kế đến ngày hiện tại theo năm

Tìm hiểu thêm về hàm DATESYTD

Sau khi tạo measure, kéo vào bảng và kết quả sẽ là Revenue lũy kế theo từng năm.

Tính % doanh số tăng trưởng so với cùng kỳ năm trước

Để tính chỉ số này, ta có thể tận dụng 2 measures đã tạo trước đó:

  • Revenue
  • Revenue SP LY

Với 2 measures này, để tính tăng trưởng so với cùng kỳ năm trước, ta viết measure như sau:

Revenue YoY = DIVIDE([Revenue] - [Revenue SP LY], [Revenue SP LY])

Thêm measure vào bảng để thấy kết quả.

Rồi format kết quả thành % để dễ hình dùng hơn về sự thay đổi.

Lưu ý: trường hợp -100% có nghĩa là do chưa có dữ liệu, chúng ta có thể chỉ định nếu là -100% thì trả về khoảng trống hoặc giá trị mà chúng ta mong muốn.

Ứng dụng của hàm CALCULATE với hàm USERELATIONSHIP

Trước khi đi vào ứng dụng này, chúng ta hãy nhìn lại Data Model, điểm đáng chú ý ở đây là trường DateKey ở bảng Date được nối với 3 trường ở bảng Sales:

  • OrderDateKey
  • DueDateKey
  • ShipDateKey

Và chỉ có DateKey với OrderDateKey là được Active, được thể hiện bằng nét liền.

Chúng ta sẽ thấy rõ hơn khi mở cửa sổ Manage relationships.

Hiểu theo góc độ khác, chúng ta đang có tới 3 trục thời gian, và chỉ có trục DateKey với OrderDateKey là đang Active, nên các chỉ số ở các ví dụ trên như Revenue đang được tính theo trục này.

Vậy trong trường hợp chúng ta muốn tính Revenue theo trục thời gian khác, như ShipDateKey thì tính sao?

Chúng ta có thể tận dụng kết nối đã có giữa DateKey với ShipDateKey bằng cách sử dụng hàm USERELATIONSHIP kết hợp với CALCULATE, measure của chúng ta sẽ như sau:

Revenue by shipdate =

CALCULATE([Revenue], USERELATIONSHIP(Sales[ShipDateKey], 'Date'[DateKey]))

Tìm hiểu thêm về hàm USERELATIONSHIP

Khi kéo measure Revenue và Revenue by shipdate vào bảng, ta thu được kết quả như hình dưới.

Sẽ có những lúc một bảng của chúng ta có nhiều mối quan hệ với một bảng khác, hay có thể hiểu bảng đó có nhiều hơn một vài trò và chúng thường được gọi là Role playing dimension.

Tìm hiểu thêm về Role Playing Dimension trong Power BI

Sử dụng hàm CALCULATE với hàm FILTER

Kết hợp CALCULATE với FILTER để tính toán theo điều kiện đầu vào là 1 Measure

Bài toán của chúng ta ở đây là viết 1 Measure tính tổng các sản phẩm có doanh thu (Revenue) với biên lợi nhuận (Profit Margin) > 0

Bài toán này phức tạp hơn ví dụ 1 do Profit Margin là 1 Measure, không phải Column có sẵn dữ liệu. Chúng ta cũng không nên tính Profit Margin thành 1 cột cố định tại bảng Product do Profit Margin có thể thay đổi theo thời gian và các slicer khác.

Trước tiên hãy tạo một bảng với cột đầu tiên là ProductKey.

Tiếp theo, chúng ta có công thức để tính Profit Margin như sau:

% Profit Margin = (Revenue – COGS) / Revenue

COGS = cost of good sold

Với công thức trên ta có measure tương ứng là:

Profit Margin = DIVIDE( SUM(Sales[Sales Amount]) - SUM(Sales[COGS]) , sum(Sales[Sales Amount]))

Khi kéo measure vào bảng ta sẽ có Profit Margin của từng ProductKey

Để trả về Revenue của các sản phẩm với Profit Margin > 0, ta viết measure sau:

Revenue Profit Margin > 0 = CALCULATE([Revenue], FILTER('Product', [Profit Margin] > 0))
  • FILTER(‘Product’, [Profit Margin] > 0): lọc các sản phẩm có Profit Margin > 0

Tìm hiểu thêm về hàm FILTER

Đưa measure vào bảng, ta sẽ có doanh thu của các sản phẩm với Profit Margin > 0.

Như hình trên, các bạn sẽ thấy measure của chúng ta sẽ chỉ trả về kết quả với những sản phẩm có Profit Margin > 0, những sản phẩm có Profit Margin < 0 thì sẽ trả về trống vì chúng không thỏa mãn điều kiện để được đưa vào tính toán.

Lưu ý: Trong ví dụ ở phần 1, chúng ta có thể gán trực tiếp mệnh đề so sánh dạng Bảng[Tên cột] = hằng số vì phép so sánh này sẽ đi qua 1 cột dữ liệu có sẵn cụ thể.

Khi sử dụng kết hợp với FILTER(Bảng, [measure] = giá trị) thì hàm FILTER sẽ đi TỪNG DÒNG của Bảng, tính toán giá trị của measure và kiểm tra điều kiện so sánh xem có phù hợp hay không. Sau đó, hàm sẽ trả về các dòng của bảng thoả mãn điều kiện để thực thi phép tính trong tham số đầu tiên của hàm CALCULATE.

Kết luận 1 cách ngắn gọn, khi bạn muốn lọc theo điều kiện là kết quả 1 measure hoặc các điều kiện phức tạp hơn, bạn cần kết hợp với hàm FILTER.

Tạm kết

Như vậy, chúng ta đã đi qua một số ứng dụng của hàm CALCULATE, nhưng đây mới chỉ là một số ứng dụng của và còn rất nhiều ứng dụng khác nữa, chúng ta sẽ cùng tìm hiểu tiếp ở những phần sau.

Hãy theo dõi Blog cũng như kênh YouTube của Datapot để không bỏ lỡ các nội dung tiếp theo về hàm CALCULATE nhé!

Chia sẻ bài viết này

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *