Topic 26 — Executive Dashboard Using DAX Measures
Design the final executive dashboard page using DAX measures, slicers, KPI cards, trend visuals, ranking tables, and validation indicators.
Production / Cybersecurity Warning
This exercise uses synthetic training data. Do not connect Power BI to production financial systems, corporate databases, or real accounting data without authorization, sandbox testing, least-privilege access, change-control approval, and cybersecurity compliance.
Objective
Design the final executive dashboard page using DAX measures, slicers, KPI cards, trend visuals, ranking tables, and validation indicators.
Use ratios precalculated in Python and SQL Server.
Recalculate selected ratios dynamically in Power BI using DAX.
Business Scenario
The final dashboard combines the two BI worlds: the controlled SQL Server model and the dynamic DAX calculation layer inside Power BI.
| Output | Purpose |
|---|---|
| Executive Overview page | Topic output |
| DAX KPI cards | Topic output |
| Trend charts | Topic output |
| Company rankings | Topic output |
| Validation indicators | Topic output |
Step-by-Step Practice
Step 1 — Connect Power BI to SQL Server
Use Power BI Desktop to connect to Server: JCDCOMPUTER and Database: FinancialRatiosBI.
Get Data → SQL Server\nServer: JCDCOMPUTER\nDatabase: FinancialRatiosBI\nMode: ImportStep 2 — Import the Required Tables and Views
Use the SQL views for reporting and the dimension/fact tables when DAX calculations require base financial statement values.
Dim_Company\nDim_Period\nFact_Income_Statement\nFact_Balance_Sheet\nFact_Cash_Flow\nFact_Debt_Ratios\nvw_All_Financial_Ratios\nvw_Executive_Financial_Summary\nvw_Latest_Period_RatiosStep 3 — Create or Confirm Relationships
Connect the company and period dimensions to the imported fact tables and views.
Dim_Company[CompanyID] → Fact and View tables[CompanyID]\nDim_Period[PeriodID] → Fact and View tables[PeriodID]Step 4 — Add the DAX Measures
Create a new measure table named DAX Financial Measures and paste the measures below.
-- ============================================================
-- Financial Ratios Analysis in BI
-- Topic 26 - Executive Dashboard Using DAX Measures
-- ============================================================
-- ------------------------------------------------------------
-- Financial Health Score using DAX Measures
-- 100-point score: 20 points per area
-- ------------------------------------------------------------
DAX Liquidity Score =
SWITCH(
TRUE(),
[DAX Current Ratio] >= 1.8, 20,
[DAX Current Ratio] >= 1.2, 15,
[DAX Current Ratio] >= 1.0, 10,
5
)
DAX Profitability Score =
SWITCH(
TRUE(),
[DAX Net Profit Margin] >= 0.15, 20,
[DAX Net Profit Margin] >= 0.08, 15,
[DAX Net Profit Margin] >= 0.03, 10,
5
)
DAX Debt Score =
SWITCH(
TRUE(),
[DAX Debt Ratio] <= 0.35, 20,
[DAX Debt Ratio] <= 0.55, 15,
[DAX Debt Ratio] <= 0.70, 10,
5
)
DAX Cash Flow Score =
SWITCH(
TRUE(),
[DAX Operating Cash Flow to Sales] >= 0.15, 20,
[DAX Operating Cash Flow to Sales] >= 0.08, 15,
[DAX Operating Cash Flow to Sales] >= 0.03, 10,
5
)
DAX Valuation Score =
SWITCH(
TRUE(),
[Precalc Current Ratio] >= 0, 20,
10
)
DAX Financial Health Score =
[DAX Liquidity Score]
+ [DAX Profitability Score]
+ [DAX Debt Score]
+ [DAX Cash Flow Score]
+ [DAX Valuation Score]
DAX Financial Health Category =
SWITCH(
TRUE(),
[DAX Financial Health Score] >= 85, "Strong",
[DAX Financial Health Score] >= 70, "Healthy",
[DAX Financial Health Score] >= 55, "Watch",
"Risk"
)
-- ------------------------------------------------------------
-- Executive Alert Measures
-- ------------------------------------------------------------
Liquidity Alert =
IF(
[DAX Current Ratio] < 1.2,
1,
0
)
Profitability Alert =
IF(
[DAX Net Profit Margin] < 0.05,
1,
0
)
Debt Alert =
IF(
[DAX Debt Ratio] > 0.60,
1,
0
)
Cash Flow Alert =
IF(
[DAX Operating Cash Flow to Sales] < 0.05,
1,
0
)
Executive Alert Count =
[Liquidity Alert]
+ [Profitability Alert]
+ [Debt Alert]
+ [Cash Flow Alert]
Executive Dashboard Status =
IF(
[Executive Alert Count] = 0,
"No Major Alerts",
FORMAT([Executive Alert Count], "0") & " Area(s) Need Review"
)
-- ------------------------------------------------------------
-- Dashboard Narrative Measure
-- ------------------------------------------------------------
Executive Narrative =
VAR CompanyText =
SELECTEDVALUE(Dim_Company[CompanyName], "Selected companies")
VAR PeriodText =
SELECTEDVALUE(Dim_Period[YearQuarter], "selected period")
RETURN
CompanyText
& " shows a financial health category of "
& [DAX Financial Health Category]
& " for "
& PeriodText
& ". Dashboard status: "
& [Executive Dashboard Status]
& "."
Step 5 — Validate the Result
Create cards, tables, and trend visuals to confirm that the measures respond to Company, Industry, Fiscal Year, and Quarter slicers.
Expected Output
Executive dashboard page completed.
DAX-driven score and category available.
Alerts and narrative respond to slicers.Business Interpretation
This topic completes the advanced Power BI version. The same financial model can now be explored through both stored SQL results and dynamic DAX calculations.
Final Result of Topic 26
The Power BI model now supports a stronger BI architecture: stored ratios for auditability and dynamic DAX ratios for interactivity.
Next topic: Advanced Power BI publishing and storytelling.