Đây là bài viết trong chuỗi series DataAndAIStation — bài viết số 29 — nơi đội ngũ chuyên viên phân tích dữ liệu tại MCNA Technology School chia sẻ kiến thức chuyên sâu về Data & AI ứng dụng thực tế. Hôm nay, chúng ta xây dựng Investment KPIs trong Power BI — bao gồm Sharpe Ratio, Maximum Drawdown, Volatility và Sortino Ratio — bộ chỉ số mà bất kỳ Financial Data Analyst nào cũng cần nắm vững để đo lường hiệu quả và rủi ro danh mục đầu tư bằng DAX.
#DataAndAIStation #Sharing29 #DataAndAIStation29
Tại sao cần Investment KPIs trong Power BI để theo dõi Sharpe và Drawdown?
Khi phân tích danh mục đầu tư, chỉ nhìn vào return (lợi nhuận) là chưa đủ. Một danh mục tăng 30% trong năm nghe rất hấp dẫn — nhưng nếu trong quá trình đó, nó từng giảm 45% từ đỉnh xuống đáy (drawdown), thì mức rủi ro đi kèm có thể khiến nhiều nhà đầu tư không chịu nổi. Đó chính là lý do bạn cần xây dựng Investment KPIs trong Power BI — những con số phản ánh cả Sharpe lẫn Drawdown, chứ không chỉ lợi nhuận đơn thuần.
Đó là lý do các chỉ số risk-adjusted return tồn tại. Chúng trả lời câu hỏi cốt lõi: “Với mỗi đơn vị rủi ro mà tôi chấp nhận, tôi nhận lại được bao nhiêu lợi nhuận?”
Trong bài viết này, chúng ta sẽ xây dựng bốn KPIs quan trọng nhất bằng DAX trong Power BI: Sharpe Ratio, Maximum Drawdown, Annualized Volatility, và Sortino Ratio. Cuối bài, chúng ta sẽ gộp tất cả vào một Risk Dashboard hoàn chỉnh.
Chuẩn bị Data Model cho Investment KPIs trong Power BI
Trước khi viết bất kỳ DAX measure nào, data model cần được thiết kế đúng. Đây là cấu trúc tối thiểu mà bạn cần:
Bảng Portfolio_Returns — chứa dữ liệu lợi nhuận hàng ngày (hoặc hàng tháng) của danh mục:
| Date | Portfolio_ID | Daily_Return | Cumulative_Value |
|------------|-------------|--------------|------------------|
| 2024-01-02 | PTF_001 | 0.0012 | 100120 |
| 2024-01-03 | PTF_001 | -0.0008 | 100040 |
| 2024-01-04 | PTF_001 | 0.0025 | 100290 |
| ... | ... | ... | ... |Bảng Dim_Date — Date table chuẩn của Power BI với đầy đủ Year, Quarter, Month, Weekday. Đây là nền tảng cho mọi time intelligence function.
Bảng Benchmark — dữ liệu return của benchmark (ví dụ: VN-Index, S&P 500) để so sánh.
Bảng Risk_Free_Rate — lãi suất phi rủi ro (ví dụ: lãi suất trái phiếu chính phủ). Có thể đơn giản chỉ là một bảng một cột, hoặc theo thời gian nếu muốn chính xác hơn.
Mối quan hệ (relationship): Dim_Date liên kết 1-nhiều với Portfolio_Returns qua cột Date, và tương tự với bảng Benchmark. Đảm bảo Dim_Date là bảng dimension chính cho trục thời gian. Khi data model đã vững, chúng ta bắt đầu xây dựng từng Investment KPI trong Power BI — khởi đầu với Sharpe Ratio.
KPI #1: Sharpe Ratio — Thước đo vàng của risk-adjusted return
Sharpe Ratio là gì?
Sharpe Ratio đo lường lợi nhuận vượt trội (excess return) mà danh mục tạo ra trên mỗi đơn vị rủi ro (volatility). Công thức kinh điển:
Sharpe Ratio = (Rp - Rf) / σp
Trong đó:
Rp = Annualized portfolio return
Rf = Risk-free rate (annualized)
σp = Annualized standard deviation of portfolio returnsÝ nghĩa: Sharpe Ratio càng cao, danh mục càng hiệu quả trong việc tạo lợi nhuận trên mỗi đơn vị rủi ro. Thông thường, Sharpe > 1.0 được coi là tốt, > 2.0 là rất tốt, và > 3.0 là xuất sắc.
Xây dựng bằng DAX — từng bước
Chúng ta cần ba measure nền tảng trước khi tính Sharpe Ratio:
Measure 1 — Average Daily Return:
Avg_Daily_Return =
AVERAGEX(
Portfolio_Returns,
Portfolio_Returns[Daily_Return]
)Measure 2 — Annualized Return:
Annualized_Return =
VAR AvgDaily = [Avg_Daily_Return]
VAR TradingDays = 252
RETURN
(1 + AvgDaily) ^ TradingDays - 1Measure 3 — Annualized Volatility (Standard Deviation):
Annualized_Volatility =
VAR DailyVol =
SQRT(
VARX.P(
Portfolio_Returns,
Portfolio_Returns[Daily_Return]
)
)
VAR TradingDays = 252
RETURN
DailyVol * SQRT(TradingDays)Measure 4 — Sharpe Ratio:
Sharpe_Ratio =
VAR AnnReturn = [Annualized_Return]
VAR RiskFree =
AVERAGE(Risk_Free_Rate[Annual_Rate])
VAR AnnVol = [Annualized_Volatility]
RETURN
IF(
AnnVol = 0,
BLANK(),
DIVIDE(AnnReturn - RiskFree, AnnVol)
)Lưu ý thực tế: Dùng VARX.P (population variance) thay vì VARX.S (sample variance) khi bạn có toàn bộ dữ liệu trong kỳ phân tích. Nếu dữ liệu là mẫu (sample), hãy dùng VARX.S. Sự khác biệt nhỏ nhưng ảnh hưởng đến tính chính xác khi số quan sát ít.
KPI #2: Maximum Drawdown — Nỗi sợ lớn nhất của nhà đầu tư
Maximum Drawdown là gì?
Maximum Drawdown (MDD) đo lường mức sụt giảm lớn nhất từ đỉnh cao nhất (peak) xuống đáy thấp nhất (trough) của giá trị danh mục, trước khi một đỉnh mới được thiết lập. Nói đơn giản: đây là kịch bản tệ nhất mà nhà đầu tư từng trải qua trong giai đoạn phân tích.
MDD = (Trough Value - Peak Value) / Peak ValueThách thức khi tính MDD trong DAX
Đây là một trong những KPI phức tạp nhất để xây dựng trong DAX, vì nó yêu cầu tính toán running maximum (đỉnh cao liên tục cập nhật) — một khái niệm vốn rất tự nhiên trong Python hoặc SQL window functions, nhưng lại đòi hỏi cách tiếp cận khác trong DAX. DAX là ngôn ngữ column-based, không phải row-based, nên các phép tính “hàng-theo-hàng” như running max sẽ cần sử dụng pattern FILTER + CALCULATE kết hợp.
Measure 5 — Cumulative Return:
Cumulative_Return =
VAR CurrentDate = MAX(Dim_Date[Date])
RETURN
CALCULATE(
PRODUCTX(
Portfolio_Returns,
1 + Portfolio_Returns[Daily_Return]
),
Dim_Date[Date] <= CurrentDate,
ALL(Dim_Date)
) - 1Measure 6 — Running Peak (High Water Mark):
Running_Peak =
VAR CurrentDate = MAX(Dim_Date[Date])
VAR CumReturnTable =
ADDCOLUMNS(
FILTER(
ALL(Dim_Date[Date]),
Dim_Date[Date] <= CurrentDate
),
"@CumReturn",
CALCULATE(
PRODUCTX(
Portfolio_Returns,
1 + Portfolio_Returns[Daily_Return]
),
Dim_Date[Date] <= EARLIER(Dim_Date[Date]),
ALL(Dim_Date)
)
)
RETURN
MAXX(CumReturnTable, [@CumReturn])Measure 7 — Current Drawdown (tại mỗi thời điểm):
Current_Drawdown =
VAR CumReturn = [Cumulative_Return] + 1
VAR Peak = [Running_Peak]
RETURN
IF(
Peak = 0,
BLANK(),
DIVIDE(CumReturn - Peak, Peak)
)Measure 8 — Maximum Drawdown:
Max_Drawdown =
VAR DrawdownTable =
ADDCOLUMNS(
ALL(Dim_Date[Date]),
"@DD",
CALCULATE([Current_Drawdown])
)
RETURN
MINX(DrawdownTable, [@DD])Cảnh báo về performance: Các measure dùng pattern ADDCOLUMNS + FILTER + ALL lồng nhau sẽ chậm đáng kể khi dữ liệu lớn (trên 1000–2000 dòng). Đây là hạn chế cố hữu của DAX với row-by-row calculations. Hai cách tối ưu: (1) Tính Running_Peak và Cumulative_Return bằng calculated columns thay vì measures — hy sinh tính linh hoạt nhưng tăng tốc đáng kể. (2) Xử lý phần nặng trong Power Query (M language) trước khi đưa vào model.
KPI #3: Annualized Volatility — Đo lường độ “xóc” của danh mục
Volatility là gì?
Volatility (biến động) là độ lệch chuẩn (standard deviation) của lợi nhuận, thường được annualize (quy về năm) để dễ so sánh. Volatility cao nghĩa là giá trị danh mục dao động mạnh — có thể tăng nhiều nhưng cũng có thể giảm nhiều.
Chúng ta đã xây dựng measure Annualized_Volatility ở phần Sharpe Ratio. Bây giờ, hãy mở rộng thêm với Rolling Volatility — volatility tính trên cửa sổ trượt (ví dụ: 30 ngày gần nhất) để theo dõi sự thay đổi rủi ro theo thời gian:
Measure 9 — Rolling 30-Day Volatility:
Rolling_Volatility_30D =
VAR CurrentDate = MAX(Dim_Date[Date])
VAR StartDate = CurrentDate - 30
VAR DailyVol =
CALCULATE(
SQRT(
VARX.P(
Portfolio_Returns,
Portfolio_Returns[Daily_Return]
)
),
Dim_Date[Date] >= StartDate &&
Dim_Date[Date] <= CurrentDate,
ALL(Dim_Date)
)
RETURN
DailyVol * SQRT(252)Khi đặt measure này lên line chart với trục Date, bạn sẽ thấy rõ những giai đoạn thị trường căng thẳng (volatility tăng vọt) và yên ắng (volatility giảm). Đây là tín hiệu cực kỳ giá trị cho việc điều chỉnh vị thế danh mục.
KPI #4: Sortino Ratio — Phiên bản “thông minh hơn” của Sharpe
Sortino Ratio là gì?
Sortino Ratio cải tiến Sharpe Ratio bằng cách chỉ tính downside volatility (biến động phía giảm) thay vì tổng volatility. Logic: nhà đầu tư không sợ biến động tăng giá — họ chỉ sợ biến động giảm giá. Vì vậy, Sortino chỉ “phạt” danh mục khi nó giảm giá, không phạt khi tăng giá.
Sortino Ratio = (Rp - Rf) / σd
Trong đó:
σd = Downside deviation (chỉ tính từ các ngày có return âm)Measure 10 — Downside Deviation:
Downside_Deviation =
VAR TargetReturn = 0
VAR DownsideVar =
AVERAGEX(
Portfolio_Returns,
VAR Diff = MIN(Portfolio_Returns[Daily_Return] - TargetReturn, 0)
RETURN Diff ^ 2
)
RETURN
SQRT(DownsideVar) * SQRT(252)Measure 11 — Sortino Ratio:
Sortino_Ratio =
VAR AnnReturn = [Annualized_Return]
VAR RiskFree = AVERAGE(Risk_Free_Rate[Annual_Rate])
VAR DownDev = [Downside_Deviation]
RETURN
IF(
DownDev = 0,
BLANK(),
DIVIDE(AnnReturn - RiskFree, DownDev)
)Khi nào dùng Sortino thay Sharpe? Khi danh mục có phân phối lợi nhuận không đối xứng (skewed) — ví dụ: chiến lược options selling thường có nhiều ngày lãi nhỏ và ít ngày lỗ lớn. Trong trường hợp này, Sharpe sẽ “phạt” cả những ngày lãi (vì chúng tạo ra volatility), trong khi Sortino chỉ nhìn vào phần rủi ro thực sự.
Thiết kế Risk Dashboard hoàn chỉnh cho Investment KPIs trong Power BI
Bây giờ, gộp tất cả KPIs vào một dashboard duy nhất. Đây là layout mà chúng tôi khuyên dùng:
Hàng trên cùng — KPI Cards
Bốn card nằm ngang, mỗi card hiển thị một KPI chính: Sharpe Ratio, Max Drawdown, Annualized Volatility, và Sortino Ratio. Sử dụng conditional formatting: Sharpe > 1 hiển thị xanh lá, 0.5–1 hiển thị vàng, dưới 0.5 hiển thị đỏ. Max Drawdown dùng logic ngược: gần 0% là xanh, vượt -20% là đỏ.
Hàng giữa — Trend Charts
Hai biểu đồ line chart nằm cạnh nhau. Bên trái: Cumulative Return so với Benchmark (hai đường trên cùng một chart, dùng hai màu khác nhau). Bên phải: Rolling 30-Day Volatility để theo dõi sự biến động rủi ro theo thời gian.
Hàng dưới — Drawdown Analysis
Một area chart hiển thị Drawdown theo thời gian — fill bằng màu đỏ nhạt, với đường Max Drawdown nằm ngang làm reference line. Chart này giúp nhà đầu tư nhìn thấy ngay “vùng đau” — những giai đoạn danh mục sụt giảm mạnh nhất.
Slicers (bộ lọc)
Thêm các slicer ở đầu trang: Portfolio_ID (nếu quản lý nhiều danh mục), Date Range (cho phép chọn giai đoạn phân tích), và Benchmark (để chuyển đổi benchmark so sánh).
Mẹo nâng cao: Những điều DAX không nói cho bạn
Vấn đề 1 — Annualization assumption: Khi nhân daily volatility với √252, chúng ta giả định rằng daily returns là independent và identically distributed (i.i.d.). Trong thực tế, thị trường tài chính có hiện tượng volatility clustering (ngày biến động mạnh thường theo sau bởi ngày biến động mạnh). Điều này có nghĩa Annualized Volatility tính bằng DAX là một ước lượng gần đúng, không phải con số chính xác tuyệt đối.
Vấn đề 2 — Survivorship bias: Nếu dữ liệu của bạn chỉ chứa các cổ phiếu/quỹ còn tồn tại hiện tại (bỏ qua những quỹ đã đóng hoặc delist), các KPI sẽ bị thiên lệch tích cực. Hãy đảm bảo data pipeline của bạn giữ lại cả dữ liệu lịch sử của các tài sản không còn hoạt động.
Vấn đề 3 — Risk-free rate thay đổi: Trong các measure trên, chúng ta dùng AVERAGE cho risk-free rate. Nếu muốn chính xác hơn, hãy dùng risk-free rate tương ứng với từng thời kỳ — đặc biệt quan trọng trong giai đoạn lãi suất thay đổi nhanh (như 2022–2024).
Vấn đề 4 — DAX performance: Với dataset lớn (hàng chục nghìn dòng daily return × nhiều portfolios), các measure sử dụng ADDCOLUMNS + iterator sẽ chậm. Giải pháp: tính trước Cumulative_Return và Running_Peak trong Power Query hoặc SQL, chỉ để DAX handle phần aggregation và display logic.
Tổng hợp: 11 DAX Measures cho Investment KPIs (Sharpe, Drawdown, Volatility)
Để tiện tham khảo, đây là toàn bộ 11 measures đã xây dựng trong bài:
| # | Measure Name | Mục đích | KPI |
|---|---|---|---|
| 1 | Avg_Daily_Return |
Return trung bình ngày | Foundation |
| 2 | Annualized_Return |
Lợi nhuận quy năm | Sharpe / Sortino |
| 3 | Annualized_Volatility |
Độ biến động quy năm | Sharpe / Volatility |
| 4 | Sharpe_Ratio |
Risk-adjusted return | Sharpe |
| 5 | Cumulative_Return |
Lợi nhuận tích lũy | Drawdown |
| 6 | Running_Peak |
Đỉnh cao nhất đến thời điểm hiện tại | Drawdown |
| 7 | Current_Drawdown |
Mức sụt giảm tại mỗi thời điểm | Drawdown |
| 8 | Max_Drawdown |
Sụt giảm tối đa trong kỳ | Drawdown |
| 9 | Rolling_Volatility_30D |
Biến động 30 ngày gần nhất | Volatility |
| 10 | Downside_Deviation |
Độ lệch chuẩn phía giảm | Sortino |
| 11 | Sortino_Ratio |
Return trên mỗi đơn vị downside risk | Sortino |
Kết luận
Xây dựng Investment KPIs trong Power BI không chỉ là viết DAX — đó là quá trình dịch kiến thức tài chính thành logic dữ liệu. Sharpe Ratio cho bạn bức tranh tổng thể về hiệu quả risk-adjusted, Maximum Drawdown cho bạn kịch bản tệ nhất đã xảy ra, Volatility cho bạn mức độ “xóc” hiện tại, và Sortino cho bạn góc nhìn tinh tế hơn khi phân phối lợi nhuận không đối xứng.
Khi kết hợp cả bốn Investment KPIs trên một dashboard Power BI — với Sharpe và Drawdown làm trụ cột — bạn có một bức tranh toàn diện để trả lời câu hỏi quan trọng nhất: “Danh mục này có đáng để nắm giữ không — và nếu có, với mức rủi ro nào?”
Trong bài tiếp theo, chúng ta sẽ đi sâu vào Portfolio Attribution Analysis — phân tích xem lợi nhuận (và rủi ro) đến từ đâu trong danh mục: asset allocation, security selection, hay timing. Hãy đón đọc.
#DataAndAIStation #Sharing29 #DataAndAIStation29
Tài liệu tham khảo
📘 DAX Statistical Functions — Microsoft Docs
📘 Scatter Chart in Power BI — Microsoft Docs
📘 Risk Management — CFA Institute
📘 DAX Studio — Debug Statistical Measures
Bài viết liên quan tại MCNA
👉 Analyzing Portfolio Growth Over Time Using Power BI
👉 Using DAX to Calculate Investment Returns
👉 Analyzing Portfolio Performance in Power BI
👉 Khóa học Power BI & Business Intelligence tại MCNA
Về tác giả
Triều Đặng — Middle Data Analyst tại MCNA Technology School. Chuyên về financial data analytics, Power BI và automated reporting trong Wealth Management.
MCNA Technology School là đơn vị tiên phong đào tạo AI, Big Data, Business Intelligence tại Việt Nam. Đối tác đào tạo của 300+ doanh nghiệp: Viettel Global, Masan Group, Techcombank, VPBank, Daikin.
🌐 mcna.vn
📘 facebook.com/mcnatechnologyschool
▶️ youtube.com/@mcna.technology.school
💼 linkedin.com/company/mcna-vn
📞 0939.866.825 (Mr. Minh Khang)
📍 30 Trung Liệt, Đống Đa, Hà Nội
📍 Liền kề 44B, TT2, Văn Quán, Hà Đông, Hà Nội
📍 The BIB Space, 50B Phan Tây Hồ, Cầu Kiệu, TPHCM
