Topic 22 - Power BI DAX Ratio Measures

Topic 22 — Power BI DAX Ratio Measures

Create a DAX measure layer that recalculates core financial ratios from SQL Server financial statement views and tables.

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

Create a DAX measure layer that recalculates core financial ratios from SQL Server financial statement views and tables.

Approach A
Use ratios precalculated in Python and SQL Server.

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

Business Scenario

The first version of the course used precalculated ratios from Python and SQL Server. This topic starts the advanced version: selected ratios are recalculated dynamically in Power BI using DAX measures.

Output	Purpose
Power BI DAX measures	Topic output
Dynamic liquidity measures	Topic output
Dynamic profitability measures	Topic output
Dynamic debt measures	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 22 - Power BI DAX Ratio Measures
-- ============================================================

-- ------------------------------------------------------------
-- Base Financial Statement Measures
-- ------------------------------------------------------------

Total Revenue =
CALCULATE(
    SUM(Fact_Income_Statement[Amount]),
    Fact_Income_Statement[RatioInput] = "Revenue"
)

Gross Profit =
CALCULATE(
    SUM(Fact_Income_Statement[Amount]),
    Fact_Income_Statement[RatioInput] = "Gross Profit"
)

Operating Income =
CALCULATE(
    SUM(Fact_Income_Statement[Amount]),
    Fact_Income_Statement[RatioInput] = "Operating Income"
)

Net Income =
CALCULATE(
    SUM(Fact_Income_Statement[Amount]),
    Fact_Income_Statement[RatioInput] = "Net Income"
)

Total Assets =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Total Assets"
)

Total Liabilities =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Total Liabilities"
)

Total Equity =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Total Equity"
)

Current Assets =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Current Assets"
)

Current Liabilities =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Current Liabilities"
)

Cash =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Cash"
)

Inventory =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Inventory"
)

Operating Cash Flow =
CALCULATE(
    SUM(Fact_Cash_Flow[Amount]),
    Fact_Cash_Flow[RatioInput] = "Operating Cash Flow"
)

Free Cash Flow =
CALCULATE(
    SUM(Fact_Cash_Flow[Amount]),
    Fact_Cash_Flow[RatioInput] = "Free Cash Flow"
)

Total Debt =
CALCULATE(
    SUM(Fact_Debt_Ratios[RatioValue]),
    Fact_Debt_Ratios[RatioName] = "Total Debt"
)

Interest Coverage Input =
CALCULATE(
    AVERAGE(Fact_Debt_Ratios[RatioValue]),
    Fact_Debt_Ratios[RatioName] = "Interest Coverage Ratio"
)

-- ------------------------------------------------------------
-- Dynamic Liquidity Ratios
-- ------------------------------------------------------------

DAX Current Ratio =
DIVIDE(
    [Current Assets],
    [Current Liabilities]
)

DAX Quick Ratio =
DIVIDE(
    [Current Assets] - [Inventory],
    [Current Liabilities]
)

DAX Cash Ratio =
DIVIDE(
    [Cash],
    [Current Liabilities]
)

DAX Working Capital =
[Current Assets] - [Current Liabilities]

-- ------------------------------------------------------------
-- Dynamic Profitability Ratios
-- ------------------------------------------------------------

DAX Gross Margin =
DIVIDE(
    [Gross Profit],
    [Total Revenue]
)

DAX Operating Margin =
DIVIDE(
    [Operating Income],
    [Total Revenue]
)

DAX Net Profit Margin =
DIVIDE(
    [Net Income],
    [Total Revenue]
)

DAX Return on Assets =
DIVIDE(
    [Net Income],
    [Total Assets]
)

DAX Return on Equity =
DIVIDE(
    [Net Income],
    [Total Equity]
)

-- ------------------------------------------------------------
-- Dynamic Debt and Cash Flow Ratios
-- ------------------------------------------------------------

DAX Debt Ratio =
DIVIDE(
    [Total Liabilities],
    [Total Assets]
)

DAX Debt to Equity Ratio =
DIVIDE(
    [Total Debt],
    [Total Equity]
)

DAX Operating Cash Flow to Sales =
DIVIDE(
    [Operating Cash Flow],
    [Total Revenue]
)

DAX Free Cash Flow Margin =
DIVIDE(
    [Free Cash Flow],
    [Total Revenue]
)

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

Power BI measure table created.
Core DAX ratio measures added.
Ratios respond dynamically to Company, Industry, Fiscal Year, and Quarter slicers.

Business Interpretation

This topic teaches that Power BI can be more than a visualization tool. With DAX, it can become a dynamic financial calculation layer.

Final Result of Topic 22

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

Next topic: Topic 23 — Compare Precalculated Ratios vs DAX Ratios.

Topic 22 — Power BI DAX Ratio Measures

Crear una capa de medidas DAX que recalcula ratios financieros principales desde las views y tablas financieras de SQL Server.

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 una capa de medidas DAX que recalcula ratios financieros principales desde las views y tablas financieras de SQL Server.

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

La primera versión del curso usó ratios precalculados desde Python y SQL Server. Este tópico inicia la versión avanzada: algunos ratios se recalculan dinámicamente en Power BI usando medidas DAX.

Output	Purpose
Power BI DAX measures	Topic output
Dynamic liquidity measures	Topic output
Dynamic profitability measures	Topic output
Dynamic debt measures	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 22 - Power BI DAX Ratio Measures
-- ============================================================

-- ------------------------------------------------------------
-- Base Financial Statement Measures
-- ------------------------------------------------------------

Total Revenue =
CALCULATE(
    SUM(Fact_Income_Statement[Amount]),
    Fact_Income_Statement[RatioInput] = "Revenue"
)

Gross Profit =
CALCULATE(
    SUM(Fact_Income_Statement[Amount]),
    Fact_Income_Statement[RatioInput] = "Gross Profit"
)

Operating Income =
CALCULATE(
    SUM(Fact_Income_Statement[Amount]),
    Fact_Income_Statement[RatioInput] = "Operating Income"
)

Net Income =
CALCULATE(
    SUM(Fact_Income_Statement[Amount]),
    Fact_Income_Statement[RatioInput] = "Net Income"
)

Total Assets =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Total Assets"
)

Total Liabilities =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Total Liabilities"
)

Total Equity =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Total Equity"
)

Current Assets =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Current Assets"
)

Current Liabilities =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Current Liabilities"
)

Cash =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Cash"
)

Inventory =
CALCULATE(
    SUM(Fact_Balance_Sheet[Amount]),
    Fact_Balance_Sheet[RatioInput] = "Inventory"
)

Operating Cash Flow =
CALCULATE(
    SUM(Fact_Cash_Flow[Amount]),
    Fact_Cash_Flow[RatioInput] = "Operating Cash Flow"
)

Free Cash Flow =
CALCULATE(
    SUM(Fact_Cash_Flow[Amount]),
    Fact_Cash_Flow[RatioInput] = "Free Cash Flow"
)

Total Debt =
CALCULATE(
    SUM(Fact_Debt_Ratios[RatioValue]),
    Fact_Debt_Ratios[RatioName] = "Total Debt"
)

Interest Coverage Input =
CALCULATE(
    AVERAGE(Fact_Debt_Ratios[RatioValue]),
    Fact_Debt_Ratios[RatioName] = "Interest Coverage Ratio"
)

-- ------------------------------------------------------------
-- Dynamic Liquidity Ratios
-- ------------------------------------------------------------

DAX Current Ratio =
DIVIDE(
    [Current Assets],
    [Current Liabilities]
)

DAX Quick Ratio =
DIVIDE(
    [Current Assets] - [Inventory],
    [Current Liabilities]
)

DAX Cash Ratio =
DIVIDE(
    [Cash],
    [Current Liabilities]
)

DAX Working Capital =
[Current Assets] - [Current Liabilities]

-- ------------------------------------------------------------
-- Dynamic Profitability Ratios
-- ------------------------------------------------------------

DAX Gross Margin =
DIVIDE(
    [Gross Profit],
    [Total Revenue]
)

DAX Operating Margin =
DIVIDE(
    [Operating Income],
    [Total Revenue]
)

DAX Net Profit Margin =
DIVIDE(
    [Net Income],
    [Total Revenue]
)

DAX Return on Assets =
DIVIDE(
    [Net Income],
    [Total Assets]
)

DAX Return on Equity =
DIVIDE(
    [Net Income],
    [Total Equity]
)

-- ------------------------------------------------------------
-- Dynamic Debt and Cash Flow Ratios
-- ------------------------------------------------------------

DAX Debt Ratio =
DIVIDE(
    [Total Liabilities],
    [Total Assets]
)

DAX Debt to Equity Ratio =
DIVIDE(
    [Total Debt],
    [Total Equity]
)

DAX Operating Cash Flow to Sales =
DIVIDE(
    [Operating Cash Flow],
    [Total Revenue]
)

DAX Free Cash Flow Margin =
DIVIDE(
    [Free Cash Flow],
    [Total Revenue]
)

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

Power BI measure table created.
Core DAX ratio measures added.
Ratios respond dynamically to Company, Industry, Fiscal Year, and Quarter slicers.

Interpretación de Negocio

Este tópico enseña que Power BI puede ser más que una herramienta visual. Con DAX, puede convertirse en una capa dinámica de cálculo financiero.

Resultado Final del Topic 22

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 23 — Compare Precalculated Ratios vs DAX Ratios.