Topic 24 - Build Dynamic Financial Ratio Cards

Topic 24 — Build Dynamic Financial Ratio Cards

Build dynamic Power BI cards for liquidity, profitability, debt, and cash flow ratios using DAX measures.

SQL Server Views→Power BI Model→DAX Measures→Executive Dashboard

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

Build dynamic Power BI cards for liquidity, profitability, debt, and cash flow ratios using DAX measures.

Approach A
Use ratios precalculated in Python and SQL Server.

Approach B
Recalculate selected ratios dynamically in Power BI using DAX.

Business Scenario

Executives do not begin with raw tables. They begin with key indicators. This topic converts DAX measures into a clean executive card layer.

Output	Purpose
Ratio KPI cards	Topic output
Status measures	Topic output
Executive health labels	Topic output
Dynamic titles	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.

Power BI Connection

Get Data → SQL Server\nServer: JCDCOMPUTER\nDatabase: FinancialRatiosBI\nMode: Import

Step 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.

Recommended imports

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_Ratios

Step 3 — Create or Confirm Relationships

Connect the company and period dimensions to the imported fact tables and views.

Power BI Relationships

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.

DAX - Final Measures

-- ============================================================
-- Financial Ratios Analysis in BI
-- Topic 24 - Build Dynamic Financial Ratio Cards
-- ============================================================

-- ------------------------------------------------------------
-- Card Display Measures
-- ------------------------------------------------------------

Card Current Ratio =
FORMAT(
    [DAX Current Ratio],
    "0.00"
)

Card Quick Ratio =
FORMAT(
    [DAX Quick Ratio],
    "0.00"
)

Card Cash Ratio =
FORMAT(
    [DAX Cash Ratio],
    "0.00"
)

Card Net Profit Margin =
FORMAT(
    [DAX Net Profit Margin],
    "0.0%"
)

Card Return on Assets =
FORMAT(
    [DAX Return on Assets],
    "0.0%"
)

Card Return on Equity =
FORMAT(
    [DAX Return on Equity],
    "0.0%"
)

Card Debt Ratio =
FORMAT(
    [DAX Debt Ratio],
    "0.0%"
)

Card OCF to Sales =
FORMAT(
    [DAX Operating Cash Flow to Sales],
    "0.0%"
)

-- ------------------------------------------------------------
-- Status Measures
-- ------------------------------------------------------------

Current Ratio Status =
SWITCH(
    TRUE(),
    [DAX Current Ratio] >= 1.8, "Strong",
    [DAX Current Ratio] >= 1.2, "Healthy",
    [DAX Current Ratio] >= 1.0, "Watch",
    "Risk"
)

Net Profit Margin Status =
SWITCH(
    TRUE(),
    [DAX Net Profit Margin] >= 0.15, "Strong",
    [DAX Net Profit Margin] >= 0.08, "Healthy",
    [DAX Net Profit Margin] >= 0.03, "Watch",
    "Risk"
)

Debt Ratio Status =
SWITCH(
    TRUE(),
    [DAX Debt Ratio] <= 0.35, "Strong",
    [DAX Debt Ratio] <= 0.55, "Healthy",
    [DAX Debt Ratio] <= 0.70, "Watch",
    "Risk"
)

Cash Flow Status =
SWITCH(
    TRUE(),
    [DAX Operating Cash Flow to Sales] >= 0.15, "Strong",
    [DAX Operating Cash Flow to Sales] >= 0.08, "Healthy",
    [DAX Operating Cash Flow to Sales] >= 0.03, "Watch",
    "Risk"
)

-- ------------------------------------------------------------
-- Executive Summary Measures
-- ------------------------------------------------------------

Dynamic Company Title =
VAR SelectedCompany =
    SELECTEDVALUE(Dim_Company[CompanyName], "All Companies")
RETURN
"Financial Ratio Summary - " & SelectedCompany

Dynamic Period Title =
VAR SelectedPeriod =
    SELECTEDVALUE(Dim_Period[YearQuarter], "All Periods")
RETURN
"Selected Period: " & SelectedPeriod

Financial Ratio Card Summary =
"Liquidity: " & [Current Ratio Status]
& " | Profitability: " & [Net Profit Margin Status]
& " | Debt: " & [Debt Ratio Status]
& " | Cash Flow: " & [Cash Flow 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

Expected result

Cards respond to slicers.
Statuses update by company and period.
Dynamic titles update based on selected company and period.

Business Interpretation

This topic connects DAX calculations to executive storytelling. Ratios become signals, not just numbers.

Final Result of Topic 24

The Power BI model now supports a stronger BI architecture: stored ratios for auditability and dynamic DAX ratios for interactivity.

Next topic: Topic 25 — Build Company and Period Ratio Trends.

Topic 24 — Build Dynamic Financial Ratio Cards

Crear tarjetas dinámicas en Power BI para liquidez, rentabilidad, deuda y cash flow usando medidas DAX.

SQL Server Views→Power BI Model→DAX Measures→Executive Dashboard

Advertencia de Producción / Ciberseguridad

Este ejercicio usa data sintética de entrenamiento. No conectes Power BI a sistemas financieros de producción, bases corporativas o data contable real sin autorización, pruebas en sandbox, acceso de mínimo privilegio, aprobación de control de cambios y cumplimiento de ciberseguridad.

Objetivo

Crear tarjetas dinámicas en Power BI para liquidez, rentabilidad, deuda y cash flow usando medidas DAX.

Approach A
Usar ratios precalculados en Python y SQL Server.

Approach B
Recalcular ratios seleccionados dinámicamente en Power BI usando DAX.

Escenario de Negocio

Los ejecutivos no empiezan con tablas crudas. Empiezan con indicadores clave. Este tópico convierte medidas DAX en una capa limpia de tarjetas ejecutivas.

Output	Purpose
Ratio KPI cards	Topic output
Status measures	Topic output
Executive health labels	Topic output
Dynamic titles	Topic output

Práctica Paso a Paso

Paso 1 — Conectar Power BI a SQL Server

Usa Power BI Desktop para conectarte a Server: JCDCOMPUTER y Database: FinancialRatiosBI.

Power BI Connection

Get Data → SQL Server\nServer: JCDCOMPUTER\nDatabase: FinancialRatiosBI\nMode: Import

Paso 2 — Importar Tablas y Views Requeridas

Usa las views SQL para reporting y las tablas de dimensión/fact cuando las medidas DAX necesiten valores base de estados financieros.

Recommended imports

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_Ratios

Paso 3 — Crear o Confirmar Relaciones

Conecta las dimensiones de compañía y período con las tablas fact y views importadas.

Power BI Relationships

Dim_Company[CompanyID] → Fact and View tables[CompanyID]\nDim_Period[PeriodID] → Fact and View tables[PeriodID]

Paso 4 — Agregar las Medidas DAX

Crea una tabla de medidas llamada DAX Financial Measures y pega las medidas de abajo.

DAX - Final Measures

-- ============================================================
-- Financial Ratios Analysis in BI
-- Topic 24 - Build Dynamic Financial Ratio Cards
-- ============================================================

-- ------------------------------------------------------------
-- Card Display Measures
-- ------------------------------------------------------------

Card Current Ratio =
FORMAT(
    [DAX Current Ratio],
    "0.00"
)

Card Quick Ratio =
FORMAT(
    [DAX Quick Ratio],
    "0.00"
)

Card Cash Ratio =
FORMAT(
    [DAX Cash Ratio],
    "0.00"
)

Card Net Profit Margin =
FORMAT(
    [DAX Net Profit Margin],
    "0.0%"
)

Card Return on Assets =
FORMAT(
    [DAX Return on Assets],
    "0.0%"
)

Card Return on Equity =
FORMAT(
    [DAX Return on Equity],
    "0.0%"
)

Card Debt Ratio =
FORMAT(
    [DAX Debt Ratio],
    "0.0%"
)

Card OCF to Sales =
FORMAT(
    [DAX Operating Cash Flow to Sales],
    "0.0%"
)

-- ------------------------------------------------------------
-- Status Measures
-- ------------------------------------------------------------

Current Ratio Status =
SWITCH(
    TRUE(),
    [DAX Current Ratio] >= 1.8, "Strong",
    [DAX Current Ratio] >= 1.2, "Healthy",
    [DAX Current Ratio] >= 1.0, "Watch",
    "Risk"
)

Net Profit Margin Status =
SWITCH(
    TRUE(),
    [DAX Net Profit Margin] >= 0.15, "Strong",
    [DAX Net Profit Margin] >= 0.08, "Healthy",
    [DAX Net Profit Margin] >= 0.03, "Watch",
    "Risk"
)

Debt Ratio Status =
SWITCH(
    TRUE(),
    [DAX Debt Ratio] <= 0.35, "Strong",
    [DAX Debt Ratio] <= 0.55, "Healthy",
    [DAX Debt Ratio] <= 0.70, "Watch",
    "Risk"
)

Cash Flow Status =
SWITCH(
    TRUE(),
    [DAX Operating Cash Flow to Sales] >= 0.15, "Strong",
    [DAX Operating Cash Flow to Sales] >= 0.08, "Healthy",
    [DAX Operating Cash Flow to Sales] >= 0.03, "Watch",
    "Risk"
)

-- ------------------------------------------------------------
-- Executive Summary Measures
-- ------------------------------------------------------------

Dynamic Company Title =
VAR SelectedCompany =
    SELECTEDVALUE(Dim_Company[CompanyName], "All Companies")
RETURN
"Financial Ratio Summary - " & SelectedCompany

Dynamic Period Title =
VAR SelectedPeriod =
    SELECTEDVALUE(Dim_Period[YearQuarter], "All Periods")
RETURN
"Selected Period: " & SelectedPeriod

Financial Ratio Card Summary =
"Liquidity: " & [Current Ratio Status]
& " | Profitability: " & [Net Profit Margin Status]
& " | Debt: " & [Debt Ratio Status]
& " | Cash Flow: " & [Cash Flow Status]

Paso 5 — Validar el Resultado

Crea cards, tablas y visuales de tendencia para confirmar que las medidas respondan a slicers de Company, Industry, Fiscal Year y Quarter.

Resultado Esperado

Expected result

Cards respond to slicers.
Statuses update by company and period.
Dynamic titles update based on selected company and period.

Interpretación de Negocio

Este tópico conecta cálculos DAX con storytelling ejecutivo. Los ratios se convierten en señales, no solo en números.

Resultado Final del Topic 24

El modelo Power BI ahora soporta una arquitectura BI más fuerte: ratios almacenados para auditabilidad y ratios dinámicos DAX para interactividad.

Próximo tópico: Topic 25 — Build Company and Period Ratio Trends.